John Swanagon's Blog

When the first human being decided to write something down, he — or indeed she — created the first record. As we recorded more, we began to understand that our records have a degree of privacy to them. In other words, we don’t want to share everything we write down. Before the advent of electronic databases, we stored our records in filing cabinets and secured them with locks. Although crude, the locks provided a degree of privacy for our records. When electronic databases arrived, the filing cabinets were jettisoned and instead of “locking” our records up, we secured them with digital safeguards such as “permissions”. Despite all the safeguards available to software engineers today, software continues to be developed with a classic vulnerability that leads to data corruption, privilege elevation, unintended disclosure of records, and more. Given how digital our lives have become, the notion of hackable software is scary. Where are the safeguards protecting our medical records, credit reports, bank records, and more? And, why don’t programming languages enforce these safeguards rather than allowing them to be circumvented?

The classic vulnerability I am referring to is, of course, SQL Injection. Whether the software be built with Sun JAVA, Microsoft .NET, C or C++, PHP, or some other programming language, developers continue to write code similar to this:

Code Vulnerable to SQL Injection

What inevitably happens when code like the above is executed is outright disaster. Suppose that the variable “Query” was a sub-part of a query string of a URL. For example, “http://someurl.com/somepage.html?id=42″. In that case, anyone capable of a GET to that page would be able to change the code. Although the aforementioned URL would be okay, imagine it changing to “http://someurl.com/somepage.html?id=42;DELETE FROM Person”. That is where disaster strikes. The code would execute a SELECT query and then subsequently execute a DELETE query. Ouch. All records in your “Person” table would be deleted. That is not okay, right? Now, some database architects are careful to make sure that code runs under extremely privileged processes. In those circumstances, it would probably be okay as the delete command would be rejected. However, the point is that anyone can change the code above. No one should be able to change your code but you. That is how hackers exploit applications to steal personally identifiable information or PII. I could enumerate a number of other examples to exploit the code above, but I think my point is made.

Addressing this vulnerability is simple. Instead of using appends on SQL statements, you would use a stored procedure and pass it the needed variables to retrieve the record. This would enforce type as well as prevent the SQL code from changing. In addition to the stored procedure, you would validate the data being presented, in this case, the record identifier. To do this, you would simply cast the data to an int in order to ensure that only a valid number is being passed to the database engine and not invalid data or some kind of SQL statement. Although a little more code is required to do this, consider how the following code cannot be changed:

Code Impervious to SQL Injection

In the code above, an exception is thrown if the “id” is not a valid int. And, on top of that, the SQL being passed to the database engine remains constant and free from user or external inputs. When patterned this way, your code is safe. It is also important to note that user inputs come from a wide variety of sources, not just interactive processes. Imagine someone putting a SQL statement into a “name” field. Then imagine a background process building a SQL statement based on data in that field. In that case, the background process would inadvertently let someone execute code you do not want executed. While it is prudent to validate user input, it is even more important to make sure that you never let anyone modify your source code but you.

Given where we are today, it makes me wonder why Sun’s JAVA and Microsoft’s .NET continue to allow raw SQL statements to be passed to database engines. If newer versions of the JDK and .NET Framework did not allow raw SQL statements, developers would be forced to pattern their code in a secure manner. However, until this happens, we are going to continue to see developers code applications with unacceptable vulnerabilities. Ultimately, it may fall to government to create laws requiring applications be developed securely in order to change the behavior and protect our personal information. With the digital age more than upon us, it is pathetic that we are not doing more to protect our data. If we don’t do something, it’ll get to a point where our information is out there for anyone to steal. If that happens, we may have to go back to filing cabinets until we get a realistic grip on how to digitally protect information in our electronic databases.