Logo

dev-resources.site

for different kinds of informations.

The do’s and don’ts of dynamic SQL for SQL Server 

Published at
9/12/2019
Categories
sql
sqlinjection
sqlserver
Author
peledzohar
Categories
3 categories in total
sql
open
sqlinjection
open
sqlserver
open
Author
10 person written this
peledzohar
open
The do’s and don’ts of dynamic SQL for SQL Server 

(This is the short version (though it includes all the bullets). A longer version, including some text explanations and links is posted on my blog)

The Do's

  • Always white-list your identifiers.
    This can be easily done using system tables or views such as sys.Tables + sys.Column or information_Schema.Column.

  • Always use sysname as the data type for identifiers.
    sysname is a built in data type that is equivalent to nvarchar(128) but it’s non-nullable. SQL Server use that data type internally for all objects names.

  • Always use exec sp_executeSql to execute your dynamic SQL (unless it needs no parameters which is a rare case).

set @sql = N'select col from '+ quotename(@table) N'+ where otherCol = @value'; 

exec sp_executeSql @sql, N'@value varchar(100)', @value
Enter fullscreen mode Exit fullscreen mode
  • Always use parameters for data.
set @sql = N'select col from '+ quotename(@table) N'+ where otherCol = @value'; 
-- @table should already be white-listed before you execute the query, so that's O.K
Enter fullscreen mode Exit fullscreen mode
  • Always wrap identifiers with quotename. othereise, your statement will break as soon as it contains an identifier with a white space (or one of many other “special” chars).

The Don'ts

  • Never pass SQL commands or clauses in parameters.
set @placeholder1 = 'select a, b, c';
set @placeholder2 = 'where x = y';
set @sql = @placeholder1 + 'from tableName '+ @placeholder2;
Enter fullscreen mode Exit fullscreen mode
  • Never concatenate parameters into your dynamic SQL string.
set @sql = 'select * from table where x = '+ @x 
Enter fullscreen mode Exit fullscreen mode

Did I leave anything out? If you think I did, please leave a comment so I can improve my post. Thanks!

sqlinjection Article's
25 articles in total
Favicon
Understanding PDO in PHP and Why It is Recommended Over `mysql_*` Functions
Favicon
Learning About Security: SQL Injection
Favicon
What is SQL Injection and Know the SQLI Attacks, Prevention and Mitigation
Favicon
Understanding Batch SQL Injection: A Real-World Threat to Data Security
Favicon
Demystifying SQLMap: A Practical Guide to Web and SQL Injection Testing
Favicon
SQL INJECTION AND ITS TYPES.
Favicon
How to Secure PHP Applications from SQL Injection Attacks
Favicon
Understanding SQL Injection: A Critical Security Vulnerability🔒⚠️🛡️
Favicon
Web Theory - Part 3 : danger! introduction to 25 types of web attacks!
Favicon
Protecting Against SQL Injection: An Overview of Platform Measures
Favicon
SQL Injection: Understanding the Threat and How to Avoid It
Favicon
What is SQL Injection?
Favicon
A Guide to Common Web Application Security Vulnerabilities and Mitigation
Favicon
How the privacy compromised in WordPress Websites?
Favicon
Como evitar SQL Injection utilizando client do BigQuery
Favicon
PHP security highlights
Favicon
Pipy: Protecting Kubernetes Apps from SQL Injection & XSS Attacks
Favicon
Security in Laravel: How to Protect Your App Part 1
Favicon
Handling Injection Attacks in Java
Favicon
SQL injection
Favicon
Common SQL Injections to Watch Out For
Favicon
SQL Injection cheat sheet
Favicon
The do’s and don’ts of dynamic SQL for SQL Server 
Favicon
4 SQL Injection Techniques For Stealing Data
Favicon
At least 36 millions of WordPress websites vulnerable

Featured ones: