But wait there's more. Last time, we talked about buffer overflow and the stack corruption attack. This time, we'll talk about an equally popular exploit structured query language or SQL injection. Like the buffer overflow situation, SQL injection can be prevented by coding policies. But what is SQL injection? SQL injection is possible when free form user input is copied character for character into a legitimate SQL statement which is executed against a database. To make SQL injection work, it helps to know the names of the data tables in the database and of course it helps to know a bit of SQL. The SQL constructs you need to know about are basically how to write a SQL statement and the fact that the double dash is the comments delimiter. Much like the double slash in the C languages and Java are comment delimiters. Let's say we want to authenticate a user. We have a login screen which contains fields for a user ID and a password. The data entered in the login text box is copied directly into the SQL statement generated in the code. The SQL statement is sent to the database and executed. In this case, the query would return one row with the user ID of the property logged in user. If a password or user ID were not in the same record in the user table, no rows would be returned by the query. This is pretty much operates the way we want it to. But wait, if we craft some special data, we can login as anybody. We need to only enter a real username and replace the password field with a single quote followed by or true, followed by the double dash. This results in an executable SQL statement which will return the user ID of the named user without checking for a password match. Clever, huh? What's the defense? There are several. Some of which depend on the database interface you're using. Probably the easiest and most transportable method is to simply search input fields for suspicious characters and either replace them or simply abort the query. In the example above, the single quote was used. Especially in user ID fields, it's possible to have names with single quotes in them like O'Bryan. So replace the single quote with a double single quote. Double single quote is equal for a single SQL code if it's in a string. Reject statements with the double dash. Reject user IDs or passwords with spaces in them. I can hear the grumbling in the background. We're not programmers, we're designers. Right, but do you want to leave the defense against SQL injection to each individual developer? Far better to create a policy or a design detail that shows how to check for SQL injection, then code inspections can ensure that it was implemented the way you intended. I realize that this discussion of SQL injection was relatively short. I think the brevity has something to do with the familiarity. If you know SQL fairly well, figuring out what's going on here is pretty easy. If you're not so familiar with SQL then, well you know my response, you ought to learn. Okay, enough of that. I'm happy for your attention. Next time, we'll sum things up for the course.
