Protecting against SQL injection

[Tor Erik Soenvisen]

Hi,

How safe is the following code against SQL injection:

# Get user privilege
digest = sha.new(pw).hexdigest()
# Protect against SQL injection by escaping quotes
uname = uname.replace("'", "''")
sql = 'SELECT privilege FROM staff WHERE ' + \
'username=\'%s\' AND password=\'%s\'' % (uname, digest)
res = self.oraDB.query(sql)

pw is the supplied password abd uname is the supplied password.

regards

 

[Paul Rubin]

# Protect against SQL injection by escaping quotes

Don't ever do that, safe or not. Use query parameters instead.
That's what they're for.

 

[Ben Finney]

Don't ever do that, safe or not. Use query parameters instead.
That's what they're for.

More specifically: They've been debugged for just these kinds of
purposes, and every time you code an ad-hoc escaping-and-formatting
SQL query, you're inviting all the bugs that have been found and
removed before.

 

[Fredrik Lundh]

More specifically: They've been debugged for just these kinds of
purposes

in a well-designed database, the SQL parser never sees the parameter values,
so *injection* attacks are simply not possible.

</F>

 

[Steve Holden]

Hi,

How safe is the following code against SQL injection:

# Get user privilege
digest = sha.new(pw).hexdigest()
# Protect against SQL injection by escaping quotes
uname = uname.replace("'", "''")
sql = 'SELECT privilege FROM staff WHERE ' + \
'username=\'%s\' AND password=\'%s\'' % (uname, digest)
res = self.oraDB.query(sql)

pw is the supplied password abd uname is the supplied password.

Slightly safer than not doing anything to the user-supplied inputs, but
nowehere near as safe as it needs to be. Use parameterized queries!

regards
Steve

 

[Aahz]

How safe is the following code against SQL injection:

# Get user privilege
digest = sha.new(pw).hexdigest()
# Protect against SQL injection by escaping quotes
uname = uname.replace("'", "''")
sql = 'SELECT privilege FROM staff WHERE ' + \
'username=\'%s\' AND password=\'%s\'' % (uname, digest)
res = self.oraDB.query(sql)

Do yourself a favor at least and switch to using double-quotes for the
string. I also recommend switching to triple-quotes to avoid the
backslash continuation.

 

[Christoph Zwerschke]

How safe is the following code against SQL injection:

# Get user privilege
digest = sha.new(pw).hexdigest()
# Protect against SQL injection by escaping quotes
uname = uname.replace("'", "''")
sql = 'SELECT privilege FROM staff WHERE ' + \
'username=\'%s\' AND password=\'%s\'' % (uname, digest)
res = self.oraDB.query(sql)

This is definitely *not* safe.

For instance, set uname = r"\' or 1=1 --"

You must replace the backslash with a double backslash as well.
But as already suggested, you should better use query parameters.

-- Christoph