Tuesday, July 21, 2009

Preventing SQL injection (again)

Recently I had to update an old PERL program which, when it was originally written, had no sanitation of user input for SQL statements. The user input (from the web) was simply concatenated into SQL statements. This made it very vulnerable to SQL injection.

The SQL DBI used in the program did not allow parameterized queries and replacing it with a newer DBI would have required massive logic changes to the program. The solution was to figure out how to properly escape special characters present in the input. This turned out to be pretty simple if the input was surrounded by single quotes within the SQL statement. Assuming this is true, single quotes present in the input can be replace with with two single quotes. This will protect the SQL from injection.

Why? ANSI SQL says that a single quote is escaped by inserting an additional single quote directly before it. Escaping single quotes makes it very difficult if not impossible for the input to terminate the SQL string. However, this only works (at least on informix) if the input string is surround by single quotes in the SQL. Input strings surrounded by double quotes can not be escaped.

This method, combined with expanding function calls within strings, I was able to prevent SQL injection without major DBI and logic changes.

No comments:

Post a Comment