sql - Avoid string concatenation to create queries

Question

Martin Fowler in his book Patterns of enterprise application architecture says

A good rule of thumb is to avoid string concatenation to put together SQL queries

It is a practice that I use quite often, to abstract the syntax of my SQL queries from the real data of the query.

Can you explain me why this is considered a bad practice?

Answer 1

While there might be usecases where you build a prepared statement by string-concatenation before compiling it, it is always bad practice to insert query-parameters using string-concatenation for two reasons:

1. Performance: When using a prepared statement the query-syntax has to be parsed only once and the access-path has to be calculated only once for each distinct query-type. When building statements by string-concatenation parsing and optimizing has to be done for each execution of the query.

2. Security: Using string-concatenation with data provided by the user is always prone to SQL-injection-attacks. Suppose you got a statement:

   query = "select secret_data from users where userid = '" + userid_param + "'";  
   

And imagine someone sends a userid_param containing "' OR 1=1;"...

This way the only way to defend is doing 100% correct input-sanitation which might be quite hard to get right depending on the language used. When using prepared statements with a properly implemented driver the driver will isolate the statement form the query-parameters so nothing will be mixed up.