Best Practices to prevent SQL Injection

Execute Dynamic SQL queries, using SP_EXECUTESQL procedure with the parameters.

While writing dynamic SQL queries and executing them, one needs to be cautious in regards to the following.

1. Avoid concatenating the parameter variables with the query.

Example

2. Avoid executing dynamic SQL queries, using EXEC stored procedure. This approach does not support passing of parameters.

Always use SP_EXECUTESQL procedure with the parameters to execute dynamic SQL queries.

Example

Let @arg1 be the parameter supplied to the procedure, which contains the script, mentioned below.

EXEC SP_EXECUTESQL

Note

• 1. The first 2 parameters of SP_EXECUTESQL (@cmd and @parameters should always be of type nvarchar.

• 2. If the dynamic SQL requires multiple string parameters, the parameters can be written separated by commas.

Example

Here, is a complete example, which demonstrates the usage of dynamic SQL in a stored procedure in the correct way.

Guidelines to follow while using parameter in like clause in dynamic SQL

when we use the parameters supplied to a procedure in a dynamic SQL command and execute it, using EXEC procedure, there is a chance the input parameter can be used to hack into the database object.

Example

This works fine but if I pass something like this as @search_string, the code will be as follows.

This will list out every record from the dbo.My Servers table as the command, which will go to the db. Will be.

SELECT * FROM dbo.MyServers WHERE server_name LIKE '%u' OR 1=1 --%'

Here, the best practice is to embed the parameters (search string) in the dynamic SQL command and execute it, using SP_EXECUTESQL with the parameters, as shown below

If the supplied pattern matches, the query upon execution will generate the appropriate records.

If a malicious pattern is supplied, the execution will result in an empty result set . Please follow the example, stated below.

This will result in an empty resultset and our data will not show up.

Guidelines to use table/column names in dynamic SQL:

While using the table/column names as the parameters in a dynamic SQL command, the system defined function QUOTENAME should be used to enclose the table/column name with in [ and ].

Example

Here, the @tabname variable can be used to manipulate the database in a wrong way. To prevent it, @tabname should be enclosed within [and] as in this case [My Servers; drop table dbo.My Configs] will not be considered as a valid table name.

Here is the script

Output

Invalid Object name ‘MyServers;PRINT ‘HELLO’’.

Here is another example, where both column and table names are used in a dynamic SQL query.

Someone can push something dangerous through the column name.

This will print out all the Server names from your dbo.MyServers table.

This should be rewritten, as stated below.

Output

Invalid column name 'server_name FROM dbo.MyServers;PRINT 'HELLO BRO! U R HACKED

References

1. Aarafat Aldhaqm, Shukor Razak, Siti Othman, Abdulalem Aldolah,and Md Ngadi (2016). Conceptual Investigation Process Model for Managing Database Forensic Investigation Knowledge. 12 (022016), 386–394.
2. Sruthi Bandhakavi, Prithvi Bisht, P. Madhusudan, and V. N. Venkatakrishnan (2007). CANDID: Preventing Sql Injection Attacks Using Dynamic Candidate Evaluations. In Proceedings of the 14th ACM Conference on Computer and Communications Security (CCS ’07). ACM, New York, NY, USA, 12–24.
3. Martin Bravenboer, Eelco Dolstra, and Eelco Visser (2007). Preventing Injection Attacks with Syntax Embeddings. In Proceedings of the 6th International Conference on Generative Programming and Component Engineering (GPCE ’07). ACM, New York, NY, USA, 3–12.
4. Gregory Buehrer, Bruce W. Weide, and Paolo A. G. Sivilotti (2005). Using Parse Tree Validation to Prevent SQL Injection Attacks. In Proceedings of the 5th International Workshop on Software Engineering and Middleware (SEM ’05). ACM, New York NY, USA, 106–113.
5. L. Chen and L. Tao (2011). Teaching Web Security Using Portable Virtual Labs. In 2011 IEEE 11th International Conference on Advanced Learning Technologies. 491–495.