Overview of Sql Injection

SQL Injection is a major injection technique, which is used to attack data-driven Applications.

Procedures and functions that use dynamic SQL queries by concatenating the text inputs to the dynamic SQL are prone to SQL Injection attack as someone can provide extra commands/malicious text through the input parameter and when executed can result in the unexpected results.

Example

equation

Here, if the user will provide @user_id = ‘105; DROP TABLE SomeTable’, an unexpected DROP table will happen.

 

Abstract

SQL Injection is a major injection technique, which is used to attack data-driven Applications.

Procedures and functions that use dynamic SQL queries by concatenating the text inputs to the dynamic SQL are prone to SQL Injection attack as someone can provide extra commands/malicious text through the input parameter and when executed can result in the unexpected results.

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

equation

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.

equation

EXEC SP_EXECUTESQL

equation

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

equation

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

equation

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

equation

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

equation

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

equation

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.

equation

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

equation

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

equation

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.

equation

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

This should be rewritten, as stated below.

equation

Output

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

References

Select your language of interest to view the total content in your interested language

Viewing options

Flyer image
journal indexing image

Share This Article