February 14, 2003 at 8:55 am
Hi there SQL experts,
Typically, when there is a form on the public server submitting to our servers, we require that the developers put some security checks in place to validate the user input on the server side, including a reqular expression check, a length check, etc...
When it comes to SQL with Insert permissions, it really worries me, and I always wonder if there is a definitive guide for making sure that no funny stuff gets inserted to our SQL databases and what would be worst case senario.
What measures are you guys taking?
Thanks
Amir
February 14, 2003 at 9:07 am
This is an excellent whitepaper from Spy Dynamics regarding SQL Injection attacks...
Hope this helps:
February 14, 2003 at 9:21 am
Also take a look at the two papers on injection at
http://www.nextgenss.com/research/papers.html
http://www.sqlsecurity.com is also a very good resource.
Steven
February 14, 2003 at 12:17 pm
I looked at the white papers, and they both suggest using "regular expressions" such as s/[^0-9a-zA-Z]//g to verify the user's input before passing it through in a SP to SQL.
Can someone translate and elaborate, particularly with a bit of example code of how to check the user's input?
Assuming this is our SP for checking the database:
Create Proc dbo.VerifyUserPassword @login varchar(20), @Password varchar(20), @IsHacker bit output, @UserSerial int output
--Code Wanted here using regular expression or SQL alternative to verify the user input is only letters and numbers
If Code Wanted says bad
Begin
Set @IsHacker = 1
Return
End
--Otherwise return the User Serial
Select @UserSerial = UserSerial from Users where Login = @login and Password = @Password
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
February 14, 2003 at 1:13 pm
Thanks everyone, also, for the last posting that is asking to elaborate more on user input check, in general, try this link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspp/html/pdc_userinput.asp
Of course, you don't have to use ASP.NET.
February 14, 2003 at 1:28 pm
There are a lot of resources that give examples using different flavors of script languages, but thanks for that last one. My question was, and is, can someone write the validation code for inside the SP (since SQL doesn't have For/Next loops, INSTR functions, etc.) that only allow good little letters and numbers?
I know that you've already made a trip to the server at this point, and that's wasteful, etc., but I would like the SP to be bulletproof in case my next genius web page intern leaves out ASP side checks.
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
February 14, 2003 at 1:38 pm
The thing that kills you is the nice little semi-colon. For instance, imagine I had a page where I'm passing parameters like so:
http://www.myserver.net/showCustomer?id=1
And my code did something to the effect of:
strSQL = "SELECT FirstName, LastName from dbo.Customers WHERE CustomerID = " & Request.QueryString("id")
And I executed this as is, I'd get:
SELECT FirstName, LastName from dbo.Customers WHERE CustomerID = 1
The problem is an attacker could piggy-back and do the following:
http://www.myserver.net/showCustomer?id=1%3Bdelete+from+customers
In a readable format (untranslated):
http://www.myserver.net/showCustomer?id=1;delete from customers
That means my statement becomes:
SELECT FirstName, LastName from dbo.Customers WHERE CustomerID = 1
DELETE FROM customers
And since both statements are legal in the same batch, SQL Server doesn't balk. That's why the bulk of your validation has to be in the web application code.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
Edited by - bkelley on 02/14/2003 1:38:19 PM
K. Brian Kelley
@kbriankelley
February 14, 2003 at 3:30 pm
Brian (I can't believe I'm arguing with one of the God's of the forum!):
I think you're assuming that I would call this bad SQL String in the simplest form with an Execute call like this:
Set dbRS = dbConn.Execute(strSQL)
I think that is the "root of all evil" in the security hole world. I know it's more work but I think it is far more secure to use Commands with Parameters like this:
With Cmd
.ActiveConnection = dbConn
.CommandText = ".dbo.WebLogUserIn"
.CommandType = 4
.Parameters.Append Cmd.CreateParameter("@Login", adVarChar, adParamInput, 20, strAccount)
.Parameters.Append Cmd.CreateParameter("@Password", adVarChar , adParamInput, 20, strPassword)
.Parameters.Append Cmd.CreateParameter("@IPAddress", adVarChar , adParamInput, 20, strIPAddress)
Set dbRS = Cmd.Execute
End With
which happens to be a copy and paste from our login page. Now the offending phrase gets tucked into a variable and is treated as text (which is not likely to match any login names or passwords!). Inside the SP this is what is seen:
Select @UserSerial = SerialNumber, @FullName = FullName from Users where Upper(Login) = Upper(@Login) and Password = @Password
which I'm believing is rather immune to injection. I haven't been able to break it with quotes, semis or anything else. Am I still missing something?
I'm not against doing ASP-side validation, I just want the SPs to remain immune to attack whether future web gurus use my pages or write new ones. My original question was How do I write a function (version 7 style, not a real UDF) that can detect bad charactors in this user-furnished variables so I can log the attempts?
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
February 14, 2003 at 8:59 pm
Hehe, Bob, I agree with you, it is a nasty, nasty evil, but you know what? I've seen it more times than I have fingers (and I have my full set) over the last 18 months. That's what is absolutely killer to the DBA. When a coder does this, he or she nukes us out the water. Even if we force 'em to go to stored procedures, inevitably they'll do the same type of thing by appending to the end, etc. It's all together disheartening.
As an example (not of SQL injection but of an unbelievable bad practice) I offer you a story that broke this past Wednesday. If the story is true (I didn't test), FTD.com has a major vulnerability in their e-commerce site. As of yet I've not seen a rebuttal...
http://www.ntbugtraq.com/default.asp?pid=36&sid=1&A2=ind0302&L=ntbugtraq&F=P&S=&P=2823
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
Edited by - bkelley on 02/14/2003 9:01:48 PM
K. Brian Kelley
@kbriankelley
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply