6

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?

borjab
  • 11,149
  • 6
  • 71
  • 98
marcosh
  • 8,780
  • 5
  • 44
  • 74
  • 8
    Mainly because of **SQL injection** - the most prevalent weakness of a great many web application out there! Research it, read about it - stop concatenating together your SQL queries! Use **parametrized queries** instead – marc_s Feb 12 '15 at 09:18
  • @marc_s but I could without any problem escape concatenated data to avoid sql injection – marcosh Feb 12 '15 at 09:21
  • 1
    @marcosh: http://stackoverflow.com/questions/910465/avoiding-sql-injection-without-parameters – Tim Schmelter Feb 12 '15 at 09:22
  • 1
    Besides SQL Injection, parameters also deal with the fact that converting data from a good data type into a string (as you have to for string concatenation) and then back again can frequently introduce formatting/parsing issues that simply don't exist if you trust a thoroughly tested library that knows how to directly convert from `your language` to `your database` data types and back again. – Damien_The_Unbeliever Feb 12 '15 at 09:26
  • 1
    @marcosh: you **will not** be able to properly "escape" or "sanitize" your data - there are just way tooooo many ways to get around this. Don't waste your time trying - use **parametrized queries** and you've solved the problem - once and for all times! – marc_s Feb 12 '15 at 11:09
  • See also [How can I add user-supplied input to an SQL statement?](https://stackoverflow.com/questions/35163361/how-can-i-add-user-supplied-input-to-an-sql-statement) – Hans Kesting Apr 26 '23 at 09:41

1 Answers1

13

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.

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
piet.t
  • 11,718
  • 21
  • 43
  • 52
  • 5
    _Safety_ is another reason, for example to avoid localization issues that could happen if you pass a `datetime` as string(01/06/2012.... 2012-06-01). If you pass it as a sql-parameter with type `datetime` you don't need to care about formatting or client/db culture-settings. And you get type-validation for free. – Tim Schmelter Feb 12 '15 at 09:41