
Using Multi-Byte Characters to Nullify SQL Injection Sanitizing - wlrm
http://howto.hackallthethings.com/2016/06/using-multi-byte-characters-to-nullify.html
======
jcranmer
In order for this to take effect, you need: 1\. To "sanitize" SQL injection by
quoting parameters and building SQL strings manually 2\. Your quoting function
needs to be in a language that iterates over multibyte chars properly (i.e.,
you're not running naively on binary strings) 3\. The output multibyte charset
must be one of the afflicted charsets (mostly CJK charsets) 4\. Your DBMS must
be incapable of properly handling the relevant charset, and is instead
checking it in a different charset.

Point #4 makes it really hard to envision this ever being a problem, since if
you're using a CJK charset on your platform already, you're quite likely to
notice very quickly that something is horribly, horribly wrong.

~~~
hultner
I was thinking the same thing, and does people really still build SQL-strings
programmatically in the server application?

Seems like this bad practice should be dead by now, not only does it open up
for injection attacks but it also prevents the database from optimizing the
query by precompiling, data aggregation and building smarter execution plans.

~~~
rbritton
It happens a lot in junk WordPress plugins. Even with built-in support for
doing it better [1], far too many just use straight concatenation.

[1]:
[https://developer.wordpress.org/reference/classes/wpdb/prepa...](https://developer.wordpress.org/reference/classes/wpdb/prepare/)

------
Illniyar
As always just use parameterized queries. This is the best and only defense
you need against sql injection.

~~~
paol
This is absolutely good advice, but people giving it rarely acknowledge that
it isn't always possible.

A particular irritant is that most (all?) db APIs make it impossible to use
parametrized queries with IN clauses. I find myself having to implement string
escaping functions sooner or later in every project, because of stuff like
that.

~~~
icebraining
At least in Postgres, you can use ANY and pass an array of values. From what
I've read, the generated query plan is equivalent.

~~~
masklinn
> From what I've read, the generated query plan is equivalent.

I believe at one point IN (values) was just sugar for = ANY(ARRAY(values)),
don't know if that's still the case.

Still requires that your DB adapter supports array parameters though.

~~~
snuxoll
Alternatively, you can use a temp table and a join. Not ideal, but still safe
from injection and should have roughly the same overhead in the database.

------
jacquesm
In-band: bad. Out-of-band: good.

The same goes for JavaScript, escape sequences, SQL string concatenation and
so on. It always _seems_ like a great idea but it is just about impossible to
get it airtight.

Better do your signaling, commands and meta-data through another channel.

------
jeffdavis
"Web applications sanitize the apostrophe (') character in strings coming from
user input being passed to SQL statements using an escape (\\) character."

Please, please don't say this. In the SQL standard, backslash is _NOT_ the
escape character for a string literal.

PostgreSQL, starting in v8.2, began transitioning from C-style escapes (using
backslash) to SQL standard escapes (where a single quote is escaped with
another single quote). Standard behavior is the default, but can be controlled
with the variable standard_conforming_strings.

But you shouldn't have to know that anyway. Use out-of-band parameters that
are passed in the wire protocol separately from the string. Web frameworks
should already ensure this, and if they don't, they are likely broken.

If you are writing a web framework and you need to use escaping for some
reason: first, make sure you can't use parameters on the wire instead; then
read the product-specific literal parsing rules very carefully, considering
things like multibyte characters.

------
treve
This is a pretty well-known problem, but mostly irrelevant if you use UTF-8.

~~~
colejohnson66
Or, you know, just doing the right thing(tm) and using parameterized queries

~~~
ams6110
Yes. If you are building SQL by concatenating user inputs (escaped or not) you
are doing it wrong.

~~~
Klathmon
IMO building SQL by concatenating anything _feels wrong_.

I still do it, and i haven't used an ORM yet that is actually useful, but it
still feels wrong.

------
anaolykarpov
I wonder, does that 0x5c byte has to be the last one in the character or can
it also be the first, or the second one, followed by the hex value of ;, the
end of instruction in mysql?

------
Kristine1975
String literals in SQL were a mistake.

