SQL Injection in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Injection in stored procedure

Author

Topic

rajiv13579
Starting Member

2 Posts

Posted - 2009-07-13 : 02:31:17

Hi All,

I am developing an .net based website which has stored procdeures to exceute the database queries.
As per my understanding from various websites, stored procedures are also vulnerable to SQL injection attack.
Can somebody please share some checklist having exact things to be done/kept in mind while writing and calling stored procedures to protect them from sql injection.
e.g. in input validation we must check for "select, union, delete,update, OR, --, insert, %%,', Grant Control, Print,DROP,!,;,=,+,||,Concat,ASCII,admin,),Having,Group by, order by,NULL,Convert,sum,where,top,waitfor,*,count," should not be there.

Also use of SQLCommand.commandtype = CommandType.StoredProcedure
in .net appplication with parameter lowers the risk but does it totally removes the risk.

I need these kind of exact mitigations and checklist.

Cheers

zafarthesultan
Starting Member

16 Posts

Posted - 2009-07-13 : 02:49:07

1)Use client/server side validation for your input controls.
2)Use properties to set the minimum/maximum number of characters to be input.
3)Restrict submission of special characters and harmful keywords by using regular expressions.
4)Visit www.regexlib.com for regular expressions list.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-13 : 02:49:42

peso teach me this...
DECLARE @sql VARCHAR(300)
SET @sql = quotename(@sql)<---

Hope can help...but advise to wait pros with confirmation...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-13 : 03:39:53

I think Books Online can explain it better than I can.
What the function does, is to enclose the string with single quotes.
And if there are single quotes within the string, the QUOTENAME functions pads the embedded single quote with an extra single quote.

N 56°04'39.26"
E 12°55'05.63"

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-07-13 : 04:15:24

Providing that you call the procedures using ado.net's parameter collection to pass parameters and there's no dynamic SQL within the procedures, they should be safe from SQL injection.

--
Gail Shaw
SQL Server MVP

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-07-13 : 08:38:28

If you do not use dynamic SQL either in SPs or your client code you are immune from SQL injection. Use stored procs and parametarised queries and you are there. Don't consider any other way.

Any solution involving blacklists, quote substitution is wrong.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-13 : 09:28:28

Not really. Even if you use Parameters Collection at client, you are still vulnerable to SQL injection if the stored procedure uses the parameters and does dynamic SQL there.

N 56°04'39.26"
E 12°55'05.63"

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-13 : 09:37:01

The following suggestions should be considered best practices
Make no assumptions about the size, type, or content of the data that is received by your application. For example, you should make the following evaluation:

How will your application behave if an errant or malicious user enters a 10-megabyte MPEG file where your application expects a postal code?

How will your application behave if a DROP TABLE statement is embedded in a text field?

Test the size and data type of input and enforce appropriate limits. This can help prevent deliberate buffer overruns.

Test the content of string variables and accept only expected values. Reject entries that contain binary data, escape sequences, and comment characters. This can help prevent script injection and can protect against some buffer overrun exploits.

When you are working with XML documents, validate all data against its schema as it is entered.

Never build Transact-SQL statements directly from user input.

Use stored procedures to validate user input.

In multitiered environments, all data should be validated before admission to the trusted zone. Data that does not pass the validation process should be rejected and an error should be returned to the previous tier.

Implement multiple layers of validation. Precautions you take against casually malicious users may be ineffective against determined attackers. A better practice is to validate input in the user interface and at all subsequent points where it crosses a trust boundary.
For example, data validation in a client-side application can prevent simple script injection. However, if the next tier assumes that its input has already been validated, any malicious user who can bypass a client can have unrestricted access to a system.

Never concatenate user input that is not validated. String concatenation is the primary point of entry for script injection.

When you can, reject input that contains the following characters.

;
Query delimiter.

'
Character data string delimiter.

--
Comment delimiter.

/* ... */
Comment delimiters. Text between /* and */ is not evaluated by the server.

xp_
Used at the start of the name of catalog-extended stored procedures, such as xp_cmdshell.

-------------------------
R..

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-13 : 09:42:53

LIKE Clauses
if we are using a LIKE clause, wildcard characters still must be escaped:

s = s.Replace("[", "[[]");
s = s.Replace("%", "[%]");
s = s.Replace("_", "[_]");

We should review all code that calls EXECUTE, EXEC, or sp_executesql. We can use queries similar to the following to help we identify procedures that contain these statements. This query checks for 1, 2, 3, or 4 spaces after the words EXECUTE or EXEC.

SELECT object_Name(id) FROM syscomments

WHERE UPPER(text) LIKE '%EXECUTE (%'

OR UPPER(text) LIKE '%EXECUTE (%'

OR UPPER(text) LIKE '%EXECUTE (%'

OR UPPER(text) LIKE '%EXECUTE (%'

OR UPPER(text) LIKE '%EXEC (%'

OR UPPER(text) LIKE '%EXEC (%'

OR UPPER(text) LIKE '%EXEC (%'

OR UPPER(text) LIKE '%EXEC (%'

OR UPPER(text) LIKE '%SP_EXECUTESQL%'

-------------------------
R..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-13 : 09:51:26

Why are you people making things MUCH harder than necessary?
Please explain, because I don't understand why people chose the harder way.

Example:

CREATE PROCEDURE dbo.uspMyProcedure
(
@TableName SYSNAME,
@ColumnName1 SYSNAME,
@ColumnName2 SYSNAME,
@Value VARCHAR(100)
)
AS

SET NOCOUNT ON

DECLARE @SQL VARCHAR(2000)

SET @SQL = 'SELECT ' + QUOTENAME(@ColumnName1) + ' FROM ' + QUOTENAME(@TableName) + ' WHERE ' + QUOTENAME(@ColumnName2) + ' = ' + QUOTENAME(@Value, '''')
-- PRINT @SQL
EXEC (@SQL)

It is as simple as this. Now you can try to enter any injection code possible to the stored procedure and it will fail!

Period.

N 56°04'39.26"
E 12°55'05.63"

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-07-13 : 09:55:24

quote:
Originally posted by Peso

Not really. Even if you use Parameters Collection at client, you are still vulnerable to SQL injection if the stored procedure uses the parameters and does dynamic SQL there.

Even dynamic SQL is safe, providing it is parametrised and input values are never concatenated into the string.

-- This is safe
DECLARE @sSQL nvarchar(500)
DECLARE @objName varchar(100) -- assume this is input from client

SET @sSQL = 'SELECT * FROM sysobjects where name = @obj'
EXEC sp_executesql @sSQL, '@obj VARCHAR(100), @obj = @objName

------------------------------------------------------------------

-- This is not
EXECUTE ('SELECT * FROM sysobjects where name = ''' + @objName + '')

To be honest, most dynamic SQL is done the second way, not the first and hence is vulnerable. Plus the first is not an option if column names or table names have to be variables.

--
Gail Shaw
SQL Server MVP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-13 : 10:07:00

-- This is
declare @sql varchar(1000)
set @sql = 'SELECT * FROM sysobjects where name = ' + quotename(@objName, '''')
EXECUTE (@sql)

you mean the sp_executesql cannot be used when also tablenames and columnames are dynamic?

N 56°04'39.26"
E 12°55'05.63"

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-13 : 11:13:09

Hi peso and Gila

I read about sp_executesql like this

sp_executesql has the same behavior as EXECUTE with regard to batches, the scope of names, and database context. The Transact-SQL statement or batch in the sp_executesql stmt parameter is not compiled until the sp_executesql statement is executed. The contents of stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql. The sp_executesql batch cannot reference variables declared in the batch that calls sp_executesql. Local cursors or variables in the sp_executesql batch are not visible to the batch that calls sp_executesql. Changes in database context last only to the end of the sp_executesql statement.

sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement many times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.

But i can't able understand both you discussed about sp_executesql. Please explain ...me

-------------------------
R..

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-07-13 : 11:21:56

quote:
Originally posted by Peso
you mean the sp_executesql cannot be used when also tablenames and columnames are dynamic?

Of course it can. But those column names and table names can't be passed as parameters to sp_executesql. Hence they have to be concatenated into the string and hence are vulnerable to SQL injection is something like quotename is not used.

--
Gail Shaw
SQL Server MVP

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-14 : 03:31:04

Also read http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-07-14 : 07:18:18

quote:
Originally posted by Peso

... you are still vulnerable to SQL injection if the stored procedure uses the parameters and does dynamic SQL there.

Yup. That's just what I said:

quote:
Originally posted by LoztInSpace
If you do not use dynamic SQL either in SPs or your client code

quote:
Originally posted by Peso
... stuff about QUOTENAME ...

Sure. If you genuinely need SQL this dynamic then this is definitely worthwhile. Instances where table & column names are dynamic are usually wrong and unnecessary. Most of the time it is values though and they are best addressed using parameters (if only because dynamic SQL needs to be parsed which as you know is best avoided when it can be).

quote:
Originally posted by rajdaksha
..stuff about LIKE ...

True (ish). It may not work exactly as planned, but it is not actually exploitable unless, again, you use dynamic SQL. If you are using LIKE you do have to be vigilant for DoS attacks but not injections as such.

http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources