3 ms·
I disagree with the part that there is always an alternative to building strings for SQL statements. Yes, all client libraries support prepared statements. But
by ufmace 7y ago
I disagree with the part that there is always an alternative to building strings for SQL statements. Yes, all client libraries support prepared statements. But none of them support placing prepared statement fields in any part of the SQL statement.
Prepared statements work fine as long as you just have simple SELECT statements with a few WHERE values as parameters. They completely fail if you need to do any advanced SQL with anything dynamic. Like optionally add more sophisticated calculated properties to the SELECT fields, conditionally JOIN in extra tables, use any of the DB engine's more advanced XML or JSON parsing features in a conditional way, support choosing <, > or =, etc.
- wahern 7y agoThe real problem is doing ad hoc SQL string concatenation. In the situations you point out, one could (and should) write a simple formatting library that makes it easy to join string literals (the operators) with dynamic data, the same way one would write a basic prepare wrapper around a SQL driver that lacked prepared statements--a simple (and proper--not regex hack) format string parser that quotes and concatenates its arguments. Doing this ensures you'll end up structuring your code for easier auditing of code and data admixtures--there should literally be a single line in the entire code base calling the abominable "escape" routine for quoting and escaping special characters. Ironically, this is the type of thing that's really trivial in C because it's so simple to write a small state machine (while + switch loop, with a variable for escape state) for parsing the format string and building a new string character by character.