Parameterized queries are something often overlooked by developers. It either looks confusing, or can't possibly be that important, or the developer wants more fine-tuned control over their queries, the excuses go on. If you're even remotely interested in maximizing performance, reducing SQL injection risks, or are deciding whether or not its worthwhile to rewrite your code to use parameterized queries, then please read on! If you already know all about parameterized queries, just skip to the end and read the syntax. Perhaps the single greatest feature of parameterized queries is that every major database engine takes advantage of them! What follows applies to all major database engines, not just SQLite:
First things first. SQL (Structured Query Language) is an interpreted language. Every time you execute a new SQL query the contents of the query must be parsed, an execution plan developed, memory allocated, etc. Parsing SQL is an expensive operation, though usually not as expensive as accessing the underlying data the query references. The decision on when to use paramterized queries is often influenced by a couple of factors:
- Does the query involve input from the user?
- Am I executing essentially the same statement with slightly different input values repeatedly?
If your code does either of these, you should consider using parameterized queries. Take for example, the following loop:
string lookupValue;
using (SQLiteCommand cmd = cnn.CreateCommand())
{
for (int i = 0; i < 100; i++)
{
lookupValue = getSomeLookupValue(i);
cmd.CommandText = @"UPDATE [Foo] SET [Value] = [Value] + 1
WHERE [Customer] LIKE '" + lookupValue + "'";
cmd.ExecuteNonQuery();
}
} |
Now this code may look innocent enough, but it suffers some performance penalties and some security risks. First, the CommandText has to be re-evaluated every time the command is executed. SQLite must parse the statement and construct a query plan 100 times in this loop. There are also a lot of memory allocations being done here. The previously-prepared CommandText is freed, the new CommandText allocated. A statement is compiled and strings are concatenated causing even more allocations and deallocations. There are also a great number of interop calls being performed behind the scenes.
If lookupValue is unknown, provided by the user, or can be altered externally, this statement becomes a risk for an injection attack. Imagine what would happen if the lookupValue contained the string '; DELETE FROM Foo; SELECT '
Now put that statement together and you have:
UPDATE [Foo] SET [Value] = [Value] + 1
WHERE [Customer] LIKE ''; DELETE FROM Foo; SELECT ''
Now this is a disastrous injection attack. The very least you'll have to do to defend against this is double any single quotes that may appear in lookupValue, which of course is yet another step in the whole loop that will slow things down.
Parameterized queries provide a means to minimize all this impact. With a parameterized query, the CommandText is set only once at the beginning of the loop. The lookupValue becomes a parameter, assigned within the loop, and the command is executed over and over without having to do any extra parsing. Furthermore, since the parameter is a string variable and the SQL statement has already been prepared, it is not vulnerable to an injection attack.
SQLite supports named and unnamed parameters. Named parameters must appear in the SQL statement with either a $ (dollar), : (colon) or @ (at sign) prefix. Unnamed parameters consist of a single question mark ? character. Rewriting the above code to use a named parameter looks like this:
using (SQLiteCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = @"UPDATE [Foo] SET [Value] = [Value] + 1
WHERE [Customer] LIKE @lookupValue";
SQLiteParameter lookupValue = new SQLiteParameter("@lookupValue");
cmd.Parameters.Add(lookupValue); for (int i = 0; i < 100; i++)
{
lookupValue.Value = getSomeLookupValue(i);
cmd.ExecuteNonQuery();
}
}
|
The same code now using an unnamed parameter (compatible with Jet/Access)
using (SQLiteCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = @"UPDATE [Foo] SET [Value] = [Value] + 1
WHERE [Customer] LIKE ?";
SQLiteParameter lookupValue = new SQLiteParameter();
cmd.Parameters.Add(lookupValue); for (int i = 0; i < 100; i++)
{
lookupValue.Value = getSomeLookupValue(i);
cmd.ExecuteNonQuery();
}
}
|
For simplicity I didn't wrap these functions inside a transaction which would have made them significantly faster. Even still, the amount of processing that must be done for the parameterized queries are a fraction of the original. When performing a bulk update or insert, or executing a query using outside information, parameterized queries are a clear winner.
Robert