Sqlx: Injection of SQL parameters seems to fail on MySQL

[gomapsdev]

MySQL Ver 14.14 Distrib 5.6.19, for osx10.9 (x86_64) using EditLine wrapper

tmpUser := User{}
err := db.DB.Get(&tmpUser, "SELECT * FROM user WHERE email=$1", "[email protected]")

err = "Error 1054: Unknown column '$1' in 'where clause'"

This works correctly if I do this:

tmpUser := User{}
err := db.DB.Get(&tmpUser, "SELECT * FROM user WHERE email=\"[email protected]\"")

err = nil

Any ideas?

[elithrar]

First: you should never just use string replacement/concat. I assume you
know that and it was just for the example/test, but I figure I'll say it
anyway.

Second: mySQL uses ? and not $1 - try the below and refer to
http://jmoiron.github.io/sqlx/#exec

err := db.DB.Get(&tmpUser, "SELECT * FROM user WHERE email=?", "[email protected]")

Hope that helps.

On Wed, Aug 6, 2014 at 10:16 AM, gomaps [email protected] wrote:

MySQL Ver 14.14 Distrib 5.6.19, for osx10.9 (x86_64) using EditLine wrapper

tmpUser := User{}err := db.DB.Get(&tmpUser, "SELECT * FROM user WHERE email=$1", "[email protected]")

err = "Error 1054: Unknown column '$1' in 'where clause'"

This works correctly if I do this:

tmpUser := User{}err := db.DB.Get(&tmpUser, "SELECT * FROM user WHERE email="[email protected]"")

err = nil

Any ideas?

—
Reply to this email directly or view it on GitHub
https://github.com/jmoiron/sqlx/issues/78.

[gomapsdev]

elithrar, Thank you! I wasn't aware that the $ vs ? was driver specific.

For the first comment on string replace/concat, do you mean that by using '?' the parameters won't be SQL escaped to prevent SQL injection attacks?

I will close this ticket on response.

[elithrar]

Using ? parameterizes the query, which is what you want.

It does not so much "escape" (which is never 100% safe/effective) as it
separates the query and the values so that the values provided by ? aren't
considered SQL. See
http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/
for some details.

In short: use ? - hope that helps.

On Wed, Aug 6, 2014 at 10:49 AM, gomaps [email protected] wrote:

elithrar, Thank you! I wasn't aware that the $ vs ? was driver specific.

For the first comment on string replace/concat, do you mean that by using
'?' the parameters won't be SQL escaped to prevent injection?

I will close this ticket on response.

—
Reply to this email directly or view it on GitHub
https://github.com/jmoiron/sqlx/issues/78#issuecomment-51281247.

[gomapsdev]

elithrar, thanks! Perfectly clear now. You've been most helpful. Closing the thread.

[divan]

Hit this problem as well. My previous experience with database/sql was using mysql driver only, so when I read the Usage section of Readme and saw "$1", I decided that it's sqlx-specific and I should use it.

Probably worth to point out explicitly that example is for postgres only, and, perhaps, add examples for mysql driver also, as it seems to be quite popular.

[jmoiron]

This is covered in the documentation

[divan]

Great! Wouldn't it be nice to copy short excerpt of bindvars section to the README as well?
I usually don't read documentation at all if everything is clear from README, which is extensive in this case.