Are stored procedures safe against SQL injection?

To understand why use stored procedures in your application, refer this great article. One of the benefits of using stor procs is preventing SQL Injections. There is a nice article on wiki explaining what SQL Injection is. The first part of this post talks about how parameterized queries and stored procedures can help prevent sql injections. the second part will critically analyse whether so acclaimed stored procedures do prevent sql injections.Dynamic SQL query i.e. sql strings embedded in the code, which are formed without properly validating the user inputs are almost 100% vulnerable to SQL injection attacks. Examine this code fragment -

string username = Textbox1.Text;

string query = “SELECT [name], [address] FROM USERS  WHERE [username] = ‘” + username + “‘”;

This code is expected to fetch the user details, based on username. It can be a typical code listed on user maintainence screen. Now a malicious user can input in the textbox badguy';DROP TABLE USERS; SELECT * FROM Countries WHERE name LIKE ‘%
This input renders the final SQL statement as follows:

SELECT [name], [address] FROM USERS  WHERE [username] = ‘badguy';DROP TABLE USERS; SELECT * FROM Countries WHERE name LIKE ‘%’

We can see how a simple harmless query can result in big threat to your database. Parameterized stored procedures can go a long way in protecting your database applications from SQL Injection. Given no input validation, the parameterized stored procedure still does not allow you to gain access to the site.
But sometimes badly written stored procedures do not prevent injections. The important thing to do is use parameters with stored procedures. SQL injection is possible if the dynamic SQL inside the stored procedure is not handled properly. Let us see an example. 
 

CREATE PROCEDURE sp_getUser

@username varchar(200) = NULL AS

DECLARE @sql nvarchar(4000)

SELECT @sql = ‘ SELECT [name], [address] ‘ + ‘ FROM [USERS] Where [username] = ”’ + @username  + ””

EXEC (@sql)

In the above case, the variable @username is directly taken from the user input and concatenated with the string i.e. @sql. The EXEC function is being used which takes string as parameter to execute the SQL statements. Making this stored procedure vulnerable to SQL injections even though the user inputs are passed to it as parameters. The user input is enclosed in the single quotes and concatenated to a string to form SQL query. The problem lies here. Instead of the parameter being a search string to the SQL query, the user input has become the part of the query as it is enclosed inside the single quotes. If the user enters the values as badguy';DROP TABLE USERS; SELECT * FROM Countries WHERE name LIKE ‘% then the final SQL query executed at the server will be

SELECT [name], [address] FROM [USERS] Where [username] = ‘badguy’;DROP TABLE USERS;  SELECT * FROM Countries WHERE name LIKE ‘%’

The user gets no benefit of the parameterised sql. The safer way to execute a dynamic sql in the stored procedure is

CREATE PROCEDURE sp_getUser

@username varchar(200) = NULL

AS

DECLARE @sql nvarchar(4000)

SELECT @sql = ‘ SELECT [name], [address] ‘ + FROM [USERS] Where [username] = ‘

SELECT @sql = @sql + ‘ [username] LIKE @username’

EXEC sp_executesql @sql, N‘@username varchar(200)’, @username

Why is this stored procedure different and safer from the previous one?

  1. The user input is not enclosed inside the single quotes. It is rather being passed as parameter to the SQL statement.
  2. The function sp_executesql is being used to execute with the parameter list and the parameterized SQL statements.

Measures to avoid SQL injection

  1. Validate all input coming from the user on the server.
  2. Avoid the use of dynamic SQL queries if there an alternate method is available.
  3. Use parameterized stored procedure with embedded parameters.
  4. Execute stored procedures using a safe interface such as Callable statements in JDBC or CommandObject in ADO.
  5. Use a low privileged account to run the database.
  6. Give proper roles and privileges to the stored procedure being used in the applications.

This posting is provided “AS IS” with no warranties, and confers no rights. 

About these ads

2 Responses to “Are stored procedures safe against SQL injection?”

  1. Emma Says:

    Thanks for the knowledge transfer.

  2. Creating a Stored Procedure « My Blog Says:

    [...] Prevent SQL injection. What is SQL injection find out here: Are Stored Procedures Safe Against SQL Injection by Anubhav Goyal [...]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 37 other followers

%d bloggers like this: