Tuesday, May 15, 2007

Hints And Pitfalls In Database Development (Part 4): Do Not String-Concatenate SQL Parameter Values

Once again, this should be obvious. Unfortunately there are still plenty of articles and books out there with code examples that look something like this:

string sql = "select * from orders where orderdate = '" + myDate.ToShortDateString() + "'";

I don't know why this is the case. Most authors are well aware of the problems that might arise, and actually comment that this should not be done in real-life projects, but guess what - it will end up in real-life projects if examples like that are floating around.

So it's not really surprising people keep on writing this kind of statements. There are many reasons why this is just plain wrong.

Instead of concatenating parameter values, it's much better to use parameter placeholders, e.g.:

string sql = "select * from orders where orderdate = @myDate";

The placeholder syntax might be varying and depends on whether we are talking about the JDBC or ODBC / OLEDB / ADO.NET world (under OLEDB the syntax is defined by the underlying database specific driver).

The database API then provides some functionality to pass the actual parameter values , something like:

SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql;
cmd.Parameters.Add("@myDate", myDate);

This has several advantages:

  • Avoiding SQL injection: No need to worry that any user will enter something like "'; drop database;" into an input field. The database vendor's driver is going to take care of that.

  • Taking advantage of prepared statements: As the SQL code always stays the same, and only parameter values change on consecutive invocations, the statement can be precompiled, which guarantees better performance.

  • Independence from database language settings: Expressions like myDate.ToShortDateString() produce language-specific formats. If your customer's database was set up with another language setting, or this code is being executed from a client with different language settings, you are out of luck.

  • Improved readability: SQL code can be defined in one piece at one place, maybe inside XML, without the need of cluttering it with of string-concatenation. Often those statements can just be copied and executed 1:1 within a database query tool for testing purposes. All that is necessary is to provide the parameters and set their values manually.

Previous Posts:

Follow-Ups: