6 ms·
You don't create statements by concatenating strings. Use prepared queries with placeholders and pass values. Databases will compile the query once, then can ru
by rjmunro 7y ago
You don't create statements by concatenating strings. Use prepared queries with placeholders and pass values. Databases will compile the query once, then can run it many times.
Sometimes you have to concatenate, e.g. to specify order by clauses. This is something database vendors need to fix. There need to be ways to tell the database to modify a prepared statement without changing it's SQL. You might be able to do it with a crazy IIF, but then it's likely not to use indexes well.
- aeyes 7y agoPrepared statements on anything but Oracle are only asking for trouble, if the distribution of your parameters does not match that of the query the statement was compiled with you can easily end up with bad query plans. Furthermore these issues are hard to analyze (no parameters in logs) and even harder to solve. I have hit this problem in just about any system using prepared statements. The performance overhead of query planning is minimal compared to the headaches it prevents.
- balfirevic 7y ago> You don't create statements by concatenating strings. > Sometimes you have to concatenate, e.g. to specify order by clauses. So, should I concatenate or not? > This is something database vendors need to fix. How long do you propose I wait before giving up and using a library that allows me to safely and sanely compose queries?
- Can_Not 7y agoHow would you make a table or view that has: deleted_at TIMESTAMP created_at TIMESTAMP user_id INTEGER body TEXT all _optionally_ filterable (greater than, less than, like, equal, etc.) where applicable without an ORM, without string concatenation?