QL Injection and How to Avoid It
SQL injection is a popular way to attack websites. Developers can easily make coding mistakes that leave a site open, and the payoff from getting access to the database can be big. Coding a site with no injection vulnerabilities takes not just careful programming, but an overall discipline that prevents mistakes.
How it works
Weaknesses arise when form-processing code constructs SQL queries on the fly. The naive approach is to construct a prototype statement and fill it out with data from the form fields. A couple of examples:
Let’s suppose that some web application requests a user name and a password to access a private area. The application will pick these values and it will collect a query to send to the database. The query might look like this:
select id from users_table where username='$username' and password='$password';
Where $username and $password are replaced by the values the user has entered on the screen.
In this case, we allowed the user to write some texts that will be sent to the database without any verification. Given the case of a malicious user, he could write in the password field and in this case, the resulting query will be as follows:
select id from users_table where username='admin' and password=''or '1'='1';
This query will produce the same result as if the admin user had introduced their password correctly. So that the web application will allow access to the administration area to a user who doesn’t know the proper password.
Let’s see another example:
SELECT ADDRESS FROM CUSTOMERS WHERE LAST_NAME = ‘#1′
Excessively trusting code will accept whatever the user types into a “Last Name” field and replace “#1” with it. This works fine as long as the user types a normal last name. But let’s say this appears in the form field:
Smith’;DELETE FROM CUSTOMERS WHERE LAST_NAME = ‘Smith
Note the quotation marks. After the SELECT statement, the next statement will delete all rows with the last name of Smith from the CUSTOMERS table. A more elaborate statement could change records or do anything else. Some injection attacks can even obtain information.
Reading or modifying the database requires knowing or guessing something about its tables. Less sophisticated attackers can attempt a denial-of-service attack by blindly modifying the SQL to make it invalid. That approach might crash an application on the server or put the database into an invalid state.
The backslash () character is as dangerous as the quote. A backslash followed by a single quote tells SQL to treat it as a literal quote character, not a string delimiter. Using it at the end of a string leaves the string unterminated, and whatever comes next will be part of the string. A backslash-based SQL injection is harder to construct that one using quote characters, but not impossible.
The comment introducer, “–” (two hyphens), will make the database engine ignore the rest of the line. The character sequence “/*” will make it ignore everything until a matching “*/” comment delimiter. These don’t work within quoted strings, and not all SQL implementations support them, but they provide a venue for injection if the attacker can get outside a quoted string.
The attacker can find more options by constructing post data directly rather than going through a Web form. The code needs to validate all post data fields, not just the ones that come from form fields.
Types of attacks
To get the best chances of success, an attacker needs to learn something about the database. For open-source code, this information may already be available. In other cases, the error-based approach can yield information. The attacker performs a trial injection and hopes to get back an error page. The error report will often consist of a stack dump and a display of the failed SQL statement. This lets the attacker see what injected text can perform harm.
A variation on this is a page that displays the SQL output, even if it’s an error message. The page will look normal, except where the error message appears instead of expected data. This approach also lets the attacker inject arbitrary SELECT statements and see the output on the page.
Web software should catch all errors rather than letting them put up error messages that browser users can see. Not only are those pages ugly, but they can also give attackers clues on just how they can exploit bugs. It also needs to check whether SQL statements have returned an error or other anomalous data before displaying their output.
The out-of-band approach lets attackers get information even if it doesn’t go to the Web page. The injected SQL may direct the output to a file. MySQL has the INTO OUTFILE option, which writes a query’s output to a file. If the attacker can get the file back (perhaps by writing it within the Web directory), it can divulge all the database’s secrets.
Blind SQL injection is the most difficult to get results with, but it can be effective with sites using open-source software, where the database’s structure is known. Even proprietary databases will often use predictable tables names like EMPLOYEES and CUSTOMERS, so pure guesswork combined with persistence can do damage.
Defenses and their limitations
A simple defense against SQL injection is to check all user inputs for risky characters and delete them, replace them with something harmless, or reject the input. It’s called sanitizing the input. This approach works in principle, but it’s error-prone. If the programmer neglects to sanitize every single input, that leaves a hole open to attackers. Several other defenses are better.
Prepared statements avoid the problem by letting the SQL engine handle the variables instead of processing them directly in the code. The statement is pre-parsed, so user data can’t change its syntax. In the example above, the code would create a prepared statement from this template:
SELECT ADDRESS FROM CUSTOMERS WHERE LAST_NAME = ‘?’
The code would give the prepared statement and the form input to the SQL engine. If a user attempts SQL injection on the LAST_NAME field, the SELECT statement simply won’t get a match. Sanitization still offers benefits, since other code may have trouble handling strangely formed inputs, but it’s not a necessity.
Stored procedures are another approach to preventing injection. A stored procedure, like a prepared statement, take parameters rather than blindly concatenating pieces into an SQL statement. In most cases this is safe. However, stored procedures can incorporate dynamic SQL, and developers sometimes do this to increase their flexibility. A dynamic SQL fragment incorporated into a stored procedure has all the vulnerability of a generated SQL statement. Developers either need to restrict stored procedures to safe ones or sanitize parameters.
The specific case of the login fields we used in the example above is just a possibility of the many more that can happen. Any field requested to the user can be used to inject SQL in the application through that field. But it is also possible to inject SQL via a URL with parameters, through a contact form or even by using a mobile application or an integration API.
The best way to detect if your application is vulnerable is by looking at its source code. If in Java, PHP, Objective-C (iPhone & iPad), Ruby or SQL parts there is a query that collects a parameter and inserts it directly into that query, then it means that your application is vulnerable. At the very least, it is potentially vulnerable.
Fixing a potential SQL injection bug
There are several alternatives. Each programming language or each developer may use the most comfortable or most convenient one in each case. We suggest using the SQL prepared statements, that consist of compiling the query before placing the parameters. After that, the parameters are placed, but they cannot modify the query. In the case above, we would obtain this result if we programmed it in PHP:
$stmt = $mysqli -> prepare("SELECT id FROM users_table WHERE username=? AND password=?");
$stmt -> bind_param("ss", $user, $pass);
$stmt -> execute();
As you can see, many serious errors can be easily detected and fixed. A code inspection tool like Kiuwan may help you detect those defects and save a lot of money.
Security through software discipline
The important thing is to use a consistent approach and never yield to the temptation to write ad hoc code. The further the code is from the raw SQL, the safer it is. Using an object-relational mapping (ORM) library eliminates the need to write SQL. Frameworks are available in many programming languages to separate application code cleanly from database access.
Any software development project that accepts user inputs for database access needs to consider the hazard of SQL injection and use programming practices that minimize its risk.
