4 ms·

I agree of course, and in the case of the article you're absolutely right, although more generally there are edge cases where parameterization doesn't work, lik

I agree of course, and in the case of the article you're absolutely right, although more generally there are edge cases where parameterization doesn't work, like giving choice of which row to order by. Naturally in such cases you should be operating on a whitelist of options and not pass the user input to the query, but the point is that some situations do still have to be accounted for.

simondotau 5y ago
I have no problem placing "if" or "switch" statements within the SQL in order to enable/disable various components of the query. The risk is when you're bringing in anything other than local constants. In the rare instances where I do this, I'm analysing the full scope of potential valid variables. I then make local copies of the variables which have been pummelled with the narrowest possible regular expression, e.g. slightly_safer = unsafe.replace(/[^0-9A-Za-z]/gi, '') Stripping away all punctuation eliminates most forms of SQLi.
- asddubs 5y ago
  for mysql at least, if you're dealing with table names you can just do a backtick escape, where you wrap the table/column name in backticks and escape any backticks within it with two backticks. That's usually my go-to in situations like this, in addition to a whitelist. anything else you can usually parameterize
  - simondotau 5y ago
    Relying on escape sequences is a serious footgun risk as there's always going to be some weird scenario that can be exploited against you, such as alternative ways to escape backticks that quietly gets enabled when some unexpected incident forces you to rebuild your MySQL Server instance. When you're bringing in strings from outside, there's no such thing as being too paranoid. White-listing of permitted characters is the only safe approach. (And if you think your whitelist might ever need contain any kind of quote-mark, backtick or backslash, then you're wrong. Your table/identifier names are stupid and must change.)

simondotau 5y ago

I have no problem placing "if" or "switch" statements within the SQL in order to enable/disable various components of the query. The risk is when you're bringing in anything other than local constants. In the rare instances where I do this, I'm analysing the full scope of potential valid variables. I then make local copies of the variables which have been pummelled with the narrowest possible regular expression, e.g. slightly_safer = unsafe.replace(/[^0-9A-Za-z]/gi, '') Stripping away all punctuation eliminates most forms of SQLi.

asddubs 5y ago
for mysql at least, if you're dealing with table names you can just do a backtick escape, where you wrap the table/column name in backticks and escape any backticks within it with two backticks. That's usually my go-to in situations like this, in addition to a whitelist. anything else you can usually parameterize
- simondotau 5y ago
  Relying on escape sequences is a serious footgun risk as there's always going to be some weird scenario that can be exploited against you, such as alternative ways to escape backticks that quietly gets enabled when some unexpected incident forces you to rebuild your MySQL Server instance. When you're bringing in strings from outside, there's no such thing as being too paranoid. White-listing of permitted characters is the only safe approach. (And if you think your whitelist might ever need contain any kind of quote-mark, backtick or backslash, then you're wrong. Your table/identifier names are stupid and must change.)