0

The Python docs state that

# Never do this -- insecure!
symbol = 'RHAT'
c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

# Do this instead
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print c.fetchone()

I understand that the first option is vulnerable to an SQL injection attack. What I don't understand is why the second option would be more secure. Aren't these identical?

Community
  • 1
  • 1
dmvianna
  • 15,088
  • 18
  • 77
  • 106

1 Answers1

1

No - the execute method will take care to escape the argument correctly. Thus, if t is Robert'); DROP TABLE Students;--, the full command that would be transmitted to the DB engine in the second case would be something like

SELECT * FROM stocks WHERE symbol='Robert\'); DROP TABLE Students;--'

which is secure (note the backslash).

EDIT: (Actually, do note the comment by CL)

Amadan
  • 191,408
  • 23
  • 240
  • 301
  • 1
    SQL does not use a backslash for escaping. Furthermore, parameters are passed directly to the database without parsing them at all (otherwise, it would not be possible to use blobs). – CL. May 09 '14 at 06:48
  • @CL.: Fair point, the latter. The former is actually not standard, and is dependent on many things, but most database engines allow escaping by backslashes, even if it is not ANSI, if certain options are set (and in many of them, the option is set by default), to the point where most people will write `\'` rather than `''`. In fact, that's exactly what PHP's `real_mysql_escape_string` does (even though I dislike the language, it points to the wide spread of the backslash escape practice). – Amadan May 09 '14 at 08:39
  • Parameters are typically not handled by quoting at all. It's more efficient — much much more efficient — to surface the parameters as points in the compiled query where the value can be directly injected without needing to parse it as if it is SQL at all. It's not _syntactic_ substitution; it's _semantic_ substitution. – Donal Fellows May 09 '14 at 13:54