SQL injection is a method of attack where an attacker can exploit vulnerable code and the type of data an application will accept, and can be exploited in any application parameter that influences a database query. Examples include parameters within the url itself, post data, or cookie values. If successful, SQL Injection can give an attacker access to backend database contents, the ability to remotely execute system commands, or in some circumstances the means to take control of the server hosting the database. Recommendations include employing a layered approach to security that includes utilizing parameterized queries when accepting user input, ensuring that only expected data is accepted by an application, and hardening the database server to prevent data from being accessed inappropriately.
Fundamentally, SQL Injection is an attack upon the web application, not the web server or the operating system itself. As the name implies, SQL Injection is the act of adding an unexpected SQL commands to a query, thereby manipulating the database in ways unintended by the database administrator or developer. When successful, data can be extracted, modified, inserted or deleted from database servers that are used by vulnerable web applications. In certain circumstances, SQL Injection can be utilized to take complete control of a system.
Each method of preventing SQL injection has its own limitations. Therefore, it is wise to employ a layered approach to preventing SQL injection, and implement several measures to prevent unauthorized access to your backend database. The following are recommended courses of action to take to prevent SQL Injection and Blind SQL Injection vulnerabilities from being exploited in your web application.
Use the following recommendations to code web applications that are not susceptible to SQL Injection attacks.
- Parameterized Queries: SQL Injection arises from an attacker’s manipulation of query data to modify query logic. The best method of preventing SQL Injection attacks is thereby to separate the logic of a query from its data. This will prevent commands inserted from user input from being executed. The downside of this approach is that it can have an impact on performance, albeit slight, and that each query on the site must be structured in this method for it to be completely effective. If one query is inadvertently bypassed, that could be enough to leave the application vulnerable to SQL Injection. The following code shows a sample SQL statement that is SQL injectable.
- sSql = “SELECT LocationName FROM Locations “;
sSql = sSql + “ WHERE LocationID = “ + Request[“LocationID”];
oCmd.CommandText = sSql;
- The following example utilizes parameterized queries, and is safe from SQL Injection attacks.
- sSql = “SELECT * FROM Locations “;
sSql = sSql + “ WHERE LocationID = @LocationID”;
oCmd.CommandText = sSql;
The application will send the SQL statement to the server without including the user’s input. Instead, a parameter-@LocationID- is used as a placeholder for that input. In this way, user input never becomes part of the command that SQL executes. Any input that an attacker inserts will be effectively negated. An error would still be generated, but it would be a simple data-type conversion error, and not something which a hacker could exploit.
- The following code samples show a product ID being obtained from an HTTP query string, and used in a SQL query. Note how the string containing the “SELECT” statement passed to SqlCommand is simply a static string, and is not concatenated from input. Also note how the input parameter is passed using a SqlParameter object, whose name (“@pid”) matches the name used within the SQL query.
- C# sample:
- string connString = WebConfigurationManager.ConnectionStrings[“myConn”].ConnectionString;
using (SqlConnection conn = new SqlConnection(connString))
SqlCommand cmd = new SqlCommand(“SELECT Count(*) FROM Products WHERE ProdID=@pid”, conn);
SqlParameter prm = new SqlParameter(“@pid”, SqlDbType.VarChar, 50);
prm.Value = Request.QueryString[“pid”];
int recCount = (int)cmd.ExecuteScalar();
- VB.NET sample:
- Dim connString As String = WebConfigurationManager.ConnectionStrings(“myConn”).ConnectionString
Using conn As New SqlConnection(connString)
Dim cmd As SqlCommand = New SqlCommand(“SELECT Count(*) FROM Products WHERE ProdID=@pid”, conn)
Dim prm As SqlParameter = New SqlParameter(“@pid”, SqlDbType.VarChar, 50)
prm.Value = Request.QueryString(“pid”)
Dim recCount As Integer = cmd.ExecuteScalar()
- Validate input: The vast majority of SQL Injection checks can be prevented by properly validating user input for both type and format. The best method of doing this is via “white listing”. This is defined as only accepting specific account numbers or specific account types for those relevant fields, or only accepting integers or letters of the English alphabet for others. Many developers will try to validate input by “black listing” characters, or “escaping” them. Basically, this entails rejecting known bad data, such as a single quotation mark, by placing an “escape” character in front of it so that the item that follows will be treated as a literal value. This approach is not as effective as white listing because it is impossible to know all forms of bad data ahead of time.
For Security Operations:
Use the following recommendations to help prevent SQL Injection attacks upon your web applications.
- Restrict Application Privileges: Limit user credentials so that only those rights the application needs to function are utilized. Any successful SQL Injection attack would run in the context of the user’s credential. While limiting privileges will not prevent SQL Injection attacks outright, it will make them significantly harder to enact.
- Strong SA Password Policy: Often, an attacker will need the functionality of the administrator account to utilize specific SQL commands. It is much easier to “brute force” the SA password when it is weak, and will increase the likelihood of a successful SQL Injection attack. Another option is not to use the SA account at all, and instead create specific accounts for specific purposes.
- Consistent Error Messaging Scheme: Ensure that you provide as little information to the user as possible when a database error occurs. Don’t reveal the entire error message. Error messages need to be dealt with on both the web and application server. When a web server encounters a processing error it should respond with a generic web page, or redirect the user to a standard location. Debug information, or other details that could be useful to a potential attacker, should never be revealed. Application servers, like WebSphere, often install with error messages or debug settings enabled by default. Consult your application server’s documentation for information on suppressing those error messages.
- Stored Procedures: If unused, delete SQL stored procedures such as master..Xp_cmdshell, xp_startmail, xp_sendmail, and sp_makewebtask.
SQL Injection vulnerabilities are inherently tied to the actual code of your web application. While not a fix, you can implement an emergency measure by adding a rule that incorporates a regular expression to your IDS to check for SQL Injection attacks. While this will not resolve all possible SQL injection vulnerabilities, it is simple to implement, and will require an attacker to escalate his methodology to achieve a successful attack. Regular expressions that can be utilized to do this follow.
Regex for detection of SQL meta-characters:
The above regular expression would be added into a Snort rule as follows:
alert tcp $EXTERNAL_NET any -> $HTTP_SERVERS $HTTP_PORTS (msg:”SQL Injection — Paranoid”; flow:to_server,established;uricontent:”.pl”;pcre:”/(\%27)|(\’)|(\-\-)|(%23)| (#)/i”; classtype:Web-application-attack; sid:9099; rev:5;)
Regex for typical SQL Injection attacks:
Regex for detecting SQL Injection with the UNION keyword:
the single-quote and its hex equivalent union — the keyword union
Similar expressions can be written for other SQL queries such as select, insert, update, delete, drop, and so on.
Regex for detecting SQL Injection attacks on a MS SQL Server:
Fixes for SQL Injection defects will ultimately require code based fixes. The steps detailed in the Developer and Security Operations section will provide any developer with the information necessary to remediate these issues. The following steps outline how to manually test an application for SQL Injection.
How to manually test applications for SQL Injection:
1. Open the web application you wish to test for SQL Injection defects in a browser.
2. Mouse over the links of the Web site with your cursor while paying attention to the bottom status bar. You will notice the URLs that the links point to. Try to find a URL with parameters in it. Ex. http://www.site.com/articleid.asp?id=42.
Note: If you don’t see any URL’s in the status bar, then just click on links and watch the address bar until you find a URL that has parameters.
3. Once a URL with parameters has been found, click the link and go to that page. In the Address bar you should now see the URL that was seen in the status bar.
4. There are two methods for testing scripts for SQL injection. Be sure to test each parameter value one at a time with both methods.
Method 1. Go to the address bar, click your cursor, and highlight a parameter value. For example, highlight the word value in “name=value” and replace it with a single quote (‘). It should now look like “name=’ “
Method 2. Go to the address bar, click your cursor, and put a single quote (‘) in the middle of the value. It should now look like “name=val’ue”
5. Click the ‘GO’ button. This will send your request to the Web server.
6. Analyze the response from the Web server for any error messages. Most database error messages will look similar to the examples below:
Example error 1:
Microsoft OLE DB Provider for SQL Server error ‘80040e14’
Unclosed quotation mark before the character string ’51 ORDER BY
some_name’. /some_directory/some_file.asp, line 5
Example error 2:
ODBC Error Code = S1000 (General error)
[Oracle][ODBC][Ora]ORA-00933: SQL command not properly ended
Example error 3:
Error: 1353 SQLSTATE: HY000 (ER_VIEW_WRONG_LIST)
Message: View’s SELECT and view’s field list have different column counts
7. Sometimes the error message is not obvious and is hidden in the source of the page. To look for it, you must view the HTML source of the page and search for the error. To do this in Internet Explorer, click the ‘View’ menu, and select the ‘Source’ option. This will cause notepad to open with the HTML source of the page. In notepad, click the ‘Edit’ menu and select ‘Find’. A dialog box will appear that will ask you to ‘Find What’. Type the phrase ‘Microsoft OLE DB’ or ‘[ODBC]’ and click ‘Find Next’.
8. If either step 6 or 7 is successful, then the Web site is vulnerable to SQL injection.
Consider a login form for a web application. If the user input from the form is directly utilized to build a dynamic SQL statement, then there has been no input validation conducted, giving control to an attacker who wants access to the database. Basically, an attacker can use an input box to send their own request to the server, and then utilize the results in a malicious manner. This is a very typical scenario considering that HTML pages often use the POST command to send parameters to another ASP page. The number in bold might be supplied by the client in an HTTP GET or POST parameter, like in the following URL:
In the example above, the client-supplied value, 12345, is simply used as a numeric expression to indicate the item that the user wants to obtain the price of an item. The web application takes this value and inserts it into the SQL statement in between the single quotes in the WHERE clause. However, consider the following URL:
http://www.example.com/GetItemPrice?ItemPrice?ItemNumber=0' UNION SELECT CreditCardNumber FROM Customers WHERE ‘1’=’1
In this case, the client-supplied value has actually modified the SQL statement itself and ‘injected’ a statement of his or her choosing. Instead of the price of an item, this statement will retrieve a customer’s credit card number.