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.

Been a while

Its been a while since my last post and a lot has happened since.

Recently (in my spare time) I have been focusing on the second version of my game engine, Mercury.
Development is picking up speed and the project is really taking shape. My personal goal is to get the engine functioning enough to make a few short games. The previous version of the engine was successfully used by the UMBC game development club for their year long 3D project. I'll probably start writing about graphics programming more than anything else.