3 ms·
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 bringin
by 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 agofor 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 agoRelying 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.)