Guard SQL Injection Attacks

Hi,

 

This blog post summarizes – How to Gurard SQL Injection Attacks.

 

Problem:

 

SQL Injection attacks are really nasty security vulnerabilities. Unfortunately developers too often neglect putting focused time on this - and leave their applications extremely vulnerable.

 

Michael Sutton recently published a very sobering post about just how widespread this issue is on the public web.  He built a C# client application that uses the Google Search API to look for sites vulnerable to SQL Injection Attacks.  The steps to achieve this were simple:

 

1.       Look for sites that have querystring values (example: search for URLs with "id=" in the URL)

2.       Send a request to the sites identified as dynamic with an altered id= statement that Adds An Extra Quote To Attempt To Cancel the SQL statement (example: id=6')

3.       Parse the response sent back to look for words like "SQL" and "query" - which typically indicate that the app is often sending back detailed error messages (also bad)

4.       Review whether the error message indicates that the parameter sent to SQL wasn't encoded correctly (in which case the site is open to SQL Injection Attacks)

 

 

SQL Injection Prone Statement	Dim SSN as String Dim SqlQuery as String SSN = Request.QueryString("SSN") SqlQuery = "SELECT au_lname, au_fname FROM authors WHERE au_id = '" + SSN + "'"
Simple Query	http://mysite.com/listauthordetails.aspx?SSN=172-32-9999   SELECT au_lname, au_fname FROM authors WHERE au_id = '172-32-9999'
Code Hack Query.	http://mysite.com/listauthordetails.aspx?SSN=172-32-9999';DROP DATABASE pubs --   SELECT au_lname, au_fname FROM authors WHERE au_id = '';DROP DATABASE pubs --   Note –   ·          The extra quote ' cancels the actual operation & makes something else. ·          The – is used to comment out the rest of the statement

 

Rather than just destroy data, a hacker could instead use the above code vulnerability to perform a JOIN that retrieves all of the data within your database and displays it on the page.

 

 

Solution:

 

1.       Don't construct dynamic SQL Statements without using a Type-Safe Parameter Encoding Mechanism.

 

Always use dynamic SQL using ADO.NET you could re-write the code above like below to make it safe:

 

Dim SSN as String = Request.QueryString("SSN")

Dim cmd As new SqlCommand("SELECT au_lname, au_fname FROM authors WHERE au_id = @au_id")
Dim param = new SqlParameter("au_id", SqlDbType.VarChar)
param.Value = SSN
cmd.Parameters.Add(param)

 

 

            MImp –

 

One common misperception is that if you are using SPROCs or a ORM you are completely safe from SQL Injection Attacks.  This isn't true - you still need to make sure you are careful when you pass values to a SPROC, and/or when you escape or customize a query with an ORM that you do it in a safe way.

 

 

2.       Always conduct a security review of your application before ever put it in production, and establish a formal security process to review all code anytime you make updates.

3.       Ensure you write automation unit tests that specifically verify your data access layer and application against SQL Injection attacks.

4.       Lock down your database to only grant the web application accessing it the minimal set of permissions that it needs to function. 

 

 

Hope this helps.

 

Arun Manglick