What exactly does "?" do in MYSQL queries?

Help & Support
1 
db.exec "update rpg_user_items set in_stash = ?", tabid

I am curious how the question mark (?) cleans the user input. I’ve been trying to find the source of its magic in https://github.com/crystal-lang/crystal-mysql, but with no luck.

  • What kind of Crystal methods does it utilize to clean (escape single quotes, etc) the user input?
  • Is it similar to PHP’s mysqli::real_escape_string?

The reason I ask is because I created a Hash to query update method, and ran into some problems where single quotes were not being escaped (possible sql injection). However, when I switched to using ?, it fixed it.

2

There is no escaping. The command and arguments are sent separately in the protocol. That is the mechanism that help you avoid sql injections and deal with manual escaping.

So ? Or $1 in pg are handled directly in the protocol itself.

3

Ohhhh!! So MYSQL handles the sql injection protection internally if ? is used? If ? is not used, it must be escaped manually?

I am so used to mysqli_real_escape_string / manually escaping input, this ? is all new to me!!

4

All drivers support commands and arguments. I suggest to use them. You should definitely have less problems.

5

Yeah I use ? a lot, I just didn’t know what they were doing internally. It makes sense now though, thank you

6

with ?
select * from user where id = ?
id= '1 or 1=1'

'1 or 1=1' always will be a part, and sql structure will never be changed.

if you do not use ?

it will add a or condition and will always be true.

7

Found this interesting answer

A parameterized query doesn’t actually do string replacement. If you use string substitution, then the SQL engine actually sees a query that looks like

SELECT * FROM mytable WHERE user='wayne'

If you use a ? parameter, then the SQL engine sees a query that looks like

SELECT * FROM mytable WHERE user=<some value>

Which means that before it even sees the string “wayne”, it can fully parse the query and understand, generally, what the query does. It sticks “wayne” into its own representation of the query, not the SQL string that describes the query. Thus, SQL injection is impossible, since we’ve already passed the SQL stage of the process.

(The above is generalized, but it more or less conveys the idea.)

You can take a look for another example in another language: SQL Bind Variables/Parameters in Databases

Also, you might want to take a look crystal-lang/crystal-db in GitHub (I can’t post the link) since it’s the base for most of Crystal database library

2 Likes