Preventing SQL Injection Attacks

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

SQL Server 2000 Forums

Transact-SQL (2000)

Preventing SQL Injection Attacks

Author

Topic

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-02-15 : 08:51:39

I was reading about SQL Injection Attacks last night and I realized that I had a serious flaw in my VB.Net code:

[CODE]
strLoginSql = "SELECT pkUserID FROM tblUsers WHERE " _
& "(tblUsers.LoginID='" & strTrimmedLogin & "') AND (tblUsers.Password='" & strTrimmedPassword & "');"
[/CODE]

I was thinking of ways I could fix this security flaw and I realized that EVERY T-SQL I end up executing whether it is straight SQL via code or a stored procedure is open to this type of attack.

What are some different ways of preventing SQL Injection Attacks? Is checking every user input and filtering out dangerous characters the only way to prevent them or is there something else that can be done?

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-02-15 : 09:04:33

Can you provide an example of a stored procedure that is vulnerable to this kind of attack? Unless you're using Dynamic SQL within the procedure, I can't think of any.

Mark

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-15 : 09:33:10

Use parameters! that's what they are there for. Even if you don't use stored procedures, you can still use parameters. basically, there is never an excuse not to use them, and they even make things easier, cleanier and more efficient -- execution plans can be cached, you don't have to worry about delimiters, and your code is shorter and clearer.

You should never, ever concatenate together user input with your sql statement. Even create sql statements in your code isn't recommended, unless you are writing a very complex "reporting" app that gives the user a lot of control over sorting, filering, groups, etc. But for 99% of the time any app accesses a database, it should be using commands with parameters.

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-02-15 : 09:37:44

jsmith8858: I did not realize that parameters turned your input into a literal value, I thought you could use the same kind of attack, but alas that is the best way to go about things.

I happened to find this article on MSDN that explains things pretty well:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/paght000002.asp

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-02-15 : 10:28:44

Parameters do not prevent SQL Injection if your stored procedure concatenates them into an SQL string for dynamic execution.

Kristen
Test

22859 Posts

Posted - 2006-02-15 : 13:31:19

I agree about Parameters, and Sprocs, but as a quick remedy you could use a function to process EACH AND EVERY variable you concatenate into a SQL command string:


strLoginSql = "SELECT pkUserID FROM tblUsers WHERE " _
    & "(tblUsers.LoginID='" & fnSQL(strTrimmedLogin) & "') " _
    & "AND (tblUsers.Password='" & fnSQL(strTrimmedPassword) & "');"

Function fnSQL(ByVal sStr)
	if isnull(sStr) then sStr = ""
	fnSQL = replace(sStr, "'", "''")
End Function

this will prevent embedded
';DROP DATABASE ...
commands from working!

Kristen

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-02-15 : 15:18:02

Is fnSQL a built-in function or is that a custom function you use to cleanse the data?

Kristen
Test

22859 Posts

Posted - 2006-02-16 : 05:59:37

"Is fnSQL a built-in function"

Nope, user defined function - you can call it whatever you like, obviously!

Some example code for it (VBScript) is in my earlier post

Kristen

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-02-16 : 07:55:07

lol I'm sorry Kristen, I can't believe I didn't see your function in that code

Kristen
Test

22859 Posts

Posted - 2006-02-16 : 07:56:22

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-16 : 08:35:24

quote:
Originally posted by blindman

Parameters do not prevent SQL Injection if your stored procedure concatenates them into an SQL string for dynamic execution.

Why would you ever do this? There is no need to built sql strings dynamically 99% of the time when you use parameters. that's the whole point of using them; don't bother if you are just going to concatenate strings together and execute them.

Are you looking for reasons why you shouldn't use parameters?

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-02-16 : 08:42:15

jsmith8858: He wasn't implying that you shouldn't use parameters. He was pointing out that you shouldn't assume that you are 100% safe just because you are using them.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-16 : 08:56:26

quote:
Originally posted by Billkamm

jsmith8858: He wasn't implying that you shouldn't use parameters. He was pointing out that you shouldn't assume that you are 100% safe just because you are using them.

If you use them *properly*, you are 100% safe. You are correct, though: just using them to eventually concatenate them into SQL strings that are executed doesn't offer you any of the security benefits of parameters other than the strict data typing.

E.g., if the parameter has a datatype of Integer, Numeric, or DateTime , even if you concatenate that value into a SQL statement and execute it, you will be safe since you can't sql injection code using only valid values for those data types.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-02-16 : 09:34:02

quote:
Originally posted by Billkamm

jsmith8858: He wasn't implying that you shouldn't use parameters. He was pointing out that you shouldn't assume that you are 100% safe just because you are using them.

Bingo. I didn't want the poster to be left with the impression that the mere use of parameters somehow eliminates the threat of SQL injection.

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-02-16 : 09:39:22

The poster was not left with that impression :)

Kristen
Test

22859 Posts

Posted - 2006-02-16 : 10:04:08

SELECT DISTINCT nuance FROM thisThread

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-02-16 : 15:40:39

What about Stored Procedure where you have to use dynamic SQL and you need to allow apostophes or other symbols as part of the parameter?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-16 : 16:15:54

You still can use parameters, even with dynamical SQL, to "substitute in" the input.

If you are creating dynamic SQL in the stored proc, use sp_executeSQL to execute it:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp

If you are creating the SQL statements dynamically at the client, you simply use place holders for parameter values in your ADO/ADO.NET command object, and then you set the parameters before executing it.

Kristen
Test

22859 Posts

Posted - 2006-02-17 : 00:47:04

And I reckon


strSQL = "select * from Customers where city = @City"
fnMyAddParameter("@City", inputCity)

has to be easier to read & debug, particularly with lots of parameters and a function wrapping every variable to double-the-quotes:


strSQL = "select * from Customers where city = '" + inputCity + "'"
strSQL = "select * from Customers where city = '" + fnMySQLStr(inputCity) + "'"

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources