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?

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
– marc_s, Commented Feb 12, 2015 at 9:18
@marc_s but I could without any problem escape concatenated data to avoid sql injection — marcosh
– marcosh, Commented Feb 12, 2015 at 9:21
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
– Damien_The_Unbeliever, Commented Feb 12, 2015 at 9:26
@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
– marc_s, Commented Feb 12, 2015 at 11:09

Tim Schmelter · Accepted Answer · 2015-02-12 09:40:22Z

14

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:

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.
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.

edited Feb 12, 2015 at 9:40

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

answered Feb 12, 2015 at 9:30

piet.t

11.9k21 gold badges45 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Schmelter Over a year ago

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.

Collectives™ on Stack Overflow

Avoid string concatenation to create queries

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related