Best Practices

Well, this book is nothing more than a draft. The pages here might turn into articles if I don't find enough to write about.

For what its worth, here's what I have so far.

SQL Injection Avoidance

SQL Injection is a serious security concern for any application that accepts user input and executes SQL created by concatenating strings including user-provided values.

How does this present a risk? Consider the following:

s = "SELECT * " +
"FROM users " +
"WHERE username = '" + username + "';"
db.execute(s)

Now consider if the user entered the following value into the login form as their username:

'; DELETE FROM users; --

The first two characters prematurely end the SELECT statement. The DELETE statement is then executed as the malicious payload. Finally the trailing comment token comments out the remainder of the statement.

So now that we recognize the problem, how can we mitigate the risk?

  1. Filter and escape user input before concatenating the SQL statement to execute.
  2. Use parameterized SQL. The parameters are guaranteed to be interpreted solely as data, not delimiters or SQL.
  3. Use stored procedures that employ parameterized SQL. If a stored procedure executes unescaped strings of SQL, it is no safer than direct execution of SQL strings, of course.
  4. Use an object relational mapper (ORM) to persist your data. The ORM will perform any necessary parameterization and escaping of user values.