Stored procedure & Sql Injection

These days I am working with stored procedure. I found this article is good to remind the stored procedure beginner. Here is the Link of the page. http://palisade.plynt.com/issues/2006Jun/injection-stored-procedures/

 

 

It is worth pointing out yet another time that in spite of strongly configured firewall rules and proper patch management systems, attacks on applications are increasing day by day. One of the main reasons is insecure coding practices. One of the widely used attack techniques on applications is SQL injection.

SQL injection is a way to manipulate the SQL statements used in web applications for querying database.  While forming the SQL query, the programmer may directly use the user input without performing any validation. This opens a door for the attacker to play around with the database present on the target machine. By sending specially crafted user input, the original SQL statement can be manipulated. These attacks are carried out on parameters in URLs, form field values and cookies.

The impact of SQL injection may vary from gathering database specific information to manipulating database information, executing system level commands to denial of service of the application. The impact also depends on the database on the target machine and the roles and privileges the SQL account is running with.

Any dynamic SQL query formed using invalidated user inputs are vulnerable to SQL injection. Some methods developers resort to in order to prevent SQL injection are parameterized queries or stored procedures.

A parameterized query is the most secure against SQL Injection attacks. But what about stored procedures? Let’s take a closer look at these in this article.

A stored procedure is a database object just like table. It is a group of SQL statements that form a logical unit and perform a particular task. It is called using the name of the stored procedure and the parameter list. Stored procedures are widely used due to the benefits like encapsulation of business logic in a single entity, strong validation, faster execution and  exception handling. But are they safe against SQL injection attacks? Not always. SQL injection is possible if the dynamic SQL inside the stored procedure is not handled properly.

Inside a stored procedure

To understand the issue better let’s consider the following stored procedure example specific to MS SQL Server. This stored procedure returns product details taking product name as search criteria.

CREATE PROCEDURE SP_ProductSearch @prodname varchar(400) = NULL ASDECLARE @sql nvarchar(4000)SELECT @sql = ' SELECT ProductID, ProductName, Category, Price ' +              ' FROM Product Where 'IF @prodname IS NOT NULL   SELECT @sql = @sql + ' ProductName LIKE ''' + @prodname + ''''EXEC (@sql)

In the above case, the variable @prodname 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. Is the above stored procedure still vulnerable to SQL injection even though the user inputs are passed to it as parameters? The answer is yes. 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 1' or '1'='1';exec master.dbo.xp_cmdshell 'dir'-- then the final SQL query executed at the server will be

SELECT ProductID, CustomerID, ProductName FROM Product Where   ProductName LIKE '1' or '1'='1';exec master.dbo.xp_cmdshell 'dir'--'

The above injected SQL query will return all the rows from the table as well as execute the operating system command DIR. (This is specific to the MS SQL server) Similar to above if the SQL query is built using concatenated string and passed as only parameter to the system stored procedure sp_executesql to execute, even then it is vulnerable to SQL injection.

CREATE PROCEDURE SP_ProductSearch @prodname varchar(400) = NULL ASDECLARE @sql nvarchar(4000)SELECT @sql = ' SELECT ProductID, ProductName, Category, Price ' +              ' FROM Product Where 'IF @prodname IS NOT NULL   SELECT @sql = @sql + ' ProductName LIKE ''' + @prodname + ''''EXECUTE sp_executesql @sql

The injection in stored procedure is not specific to MS SQL server only; this issue exists in other databases also. Wherever dynamic SQL is present and not handled properly, it is vulnerable. Even in Oracle a PL/SQL block is vulnerable to injection attack if the SQL query formed with user input enclosed and concatenated to a string instead using bind variables. Following is the sample PL/SQL code which is exposed to injection attacks.

CREATE OR REPLACE PROCEDURE SP_ProductSearch(Prodname IN VARCHAR2) AS       sql VARCHAR;       code VARCHAR;BEGIN   Sql := 'SELECT ProductID, ProductName, Category, Price WHERE' +           ' ProductName=''' || Prodname || '''';   EXECUTE IMMEDIATE sql INTO code;END;

Prevention Mechanisms

How to write the dynamic SQL in a secure way? Which function or system stored procedure to use to avoid the injection hole? Here is the same stored procedure written in secure way.

CREATE PROCEDURE SP_ProductSearch @prodname varchar(400) = NULL ASDECLARE @sql nvarchar(4000)SELECT @sql = ' SELET ProductID, ProductName, Category, Price ' +              ' FROM Product Where 'IF @prodname IS NOT NULL  SELECT @sql = @sql + ' ProductName LIKE @prodname'EXEC sp_executesql @sql, N'@prodname varchar(400)',@prodname 

This stored procedure is different from the previous one for two reasons. First - the user input is not enclosed inside the single quotes. It is rather being passed as parameter to the SQL statement. Second - the function sp_executesql is being used to execute with the parameter list and the parameterized SQL statements. The difference between the EXEC() and sp_executesql is that the former takes SQL statement in a string as parameter. But in case of the latter, it is a system procedure whose first parameter is a parameterized SQL statement. The second parameter is a parameter-list declaration, similar to the parameter list present in the declaration of a stored procedure. And the remaining are simply the parameters in that parameter-list. So the query built using this method is always same whenever it gets called but the user input changes which will be supplied at runtime. If the malicious user enters a value like ' or '1'='1, it will be passed as a parameter to the SQL statement. It won’t be part of the SQL statement any more as we have seen in vulnerable stored procedure above. It will search for the value' or '1'='1 as product name in the database. Thus preventing SQL Injection attacks.

In case of Oracle use of bind variables in the PL/SQL block may eradicate the vulnerability.

Other Measures to avoid SQL injection

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

 

 

The main point of this article is Use sp_executesql (System stored procedure) could help to avoid the sql injection for dynamic Sql.