3 ms·
Ignore that other person saying to escape the quote -- do as the wikipedia article they linked says, and use parametrized queries. This does all the escaping fo
by crux_ 17y ago
Ignore that other person saying to escape the quote -- do as the wikipedia article they linked says, and use parametrized queries. This does all the escaping for you, and unlike escaping, makes it much less likely that something will slip between the cracks.
(There's almost never a good reason to construct SQL on the fly.)
- fendale 17y agoExactly correct - and if you are using Oracle and not using parameterised queries (known as bind variables in the Oracle world), you are quickly going to have performance problems too. That's not the case with MYSQL as it doesn't have a cursor cache in the same way Oracle has. I have no idea about Postgres or SQLServer. A rule of thumb is that if you are concatenting user input strings into your SQL query strings you are doing it wrong.
- tptacek 17y agoRemember that parameterized queries aren't a panacea. There are plenty of things that don't parameterize well, such as table names, sort orders, and limits.
- deleted 17y ago[deleted]
- epochwolf 17y agoParametrized queries still use escaping. The only difference is instead of you doing the escaping and query construction you're letting someone else's code handle it for you. (Code I would assume is far better tested than yours)
- crux_ 17y agoa) More importantly, they enforce a discipline on you: never treat data as code. b) I'm guessing there are plenty of databases that execute parametrized queries entirely differently than a plaintext ones, including the absence of an escaping step. Since the whole point of quoting + escaping is to demarcate the "data" parts of a query, and you've already done that with parameters, there's no reason a query engine can't use the parameter directly rather than run it through a useless escape then parse-and-unescape cycle.
- tptacek 17y agoThis isn't really an accurate description of what's happening in a parameterized query. In most cases, parameterized queries have already been parsed by the time inputs are interpolated into them; if you look at the protocol on the wire, you can see the difference between query structure and slots. SQL Injection vulnerabilities happen when inputs influence the parsing of the query. You can't do that when the database already has an AST for the query. Parameterized queries are not just a different kind of quoting.