Sanitizing Input in Web Apps (Part 3)

Last time in our web app input sanitation series, we looked at unsanitized input as part of an HTML tag or attribute. This entry focuses on sanitizing SQL queries.

Case 3: Sanitizing SQL Query Data

The basic SQL attack takes advantage of improper sanitation to execute its own queries against a database. This can lead to a database being compromised.

Take this snippet, for example:

———————————————————————

———————————————————————

The code takes in a variable “id” and queries the database to see if that ID exists in the system. If it does, it prints out some simple HTML. The problem is that the “id” parameter is not sanitized beforehand.

A normal query would be something like:
http://example.com/user.php?id=33

Which would send the following to the database as a query:
SELECT count(*) from members where id LIKE ’33’;

However, a malicious query might look different:
http://example.com/user.php?id=33′ and password like ‘user_password

Which would send the following to the database as a query:
SELECT count(*) from members where id LIKE ’33’ and password like ‘bunnyslippers’;

This means an attacker could use the page to execute a query to find out sensitive information. In this case, if the user’s password is “bunnyslippers,” the page will display “ID found.” Depending on the way the original query is set up, there may be many things an attacker can do to take advantage of a SQL injection vulnerability.

The basic step to sanitize query input is to encode single and double quotation marks so that the query data isn’t interpreted as special SQL commands, operators, or delimiters. Although writing a function to do this is possible, PHP has a built-in function to clean up SQL query data.

Simply run all query variables through mysql_real_escape_string() and everything should be fine (with very few exceptions). In the above example, if the malicious input was filtered by the function, the SQL query that was executed would be:

SELECT count(*) from members where id LIKE ’33\’ and password like \’bunnyslippers’;

Resulting in no unintentional leak of information since the quotation marks are escaped by the function.

Security Musings features a topic to help educate our readers about security. For more information about Gemini Security Solutions’ security education capabilities, contact us!