I know... tried it several times... growing up is hard. So instead, you decided to become a "Red Teamer" (aka Pentesters...). You got the hoodie, and you acquired a taste for highly caffeinated energy drinks. Now the only thing left: Learning to write a script. So like all the other "kids," you learn Python and start writing and publishing tools (Yes... all the world needed was DNS covert channel tool #32773... you realize you could have written that as a bash oneliner?).

So what follows comes from a reluctant occasional Python coder.

So instead of learning a real language like Perl, you figured Python is it. Lately, in my ongoing quest to avoid growing up, I jumped from Perl on the Python bandwagon to also be one of the cool kids. Like most developers, I heavily lean on Stackoverflow and other tools like Github to find sample code that I am sure millions of others have reviewed (isn't that the point of Open Sources?) and made sure it is well written secure code.

But as I learn more about Python, I noticed a dangerous trend in Python: People all for sudden forgot that preparing SQL statements is a thing. I think the problem is in part that Python makes it so easy to mix up prepared/not-prepared.

Compare these two snippets:

sql = """SELECT count(*) FROM users where id=%s"""
vars = ('642063 OR 1=1',)
cursor.execute(sql , vars)
count=cursor.fetchone()
print(count[0])

Returns: 1

sql = """SELECT count(*) FROM users where id=%s"""
vars = ('642063 OR 1=1',)
cursor.execute(sql % vars)
count=cursor.fetchone()
print(count[0])

Returns: 123237

The difference is a single character in the line highlighted in red: a "," vs. a "%."

A "," will identify the variables as a second parameter. A '%' is a format string like operators, and it will alter the string, which is the only parameter in this case. So really no better than concatenating the string. Yes, the '{}' notation is not much better. You can specify more specific formats, but that falls apart quickly if you are using arbitrary strings.

Unlike with Perl's amazing DBI library, Python is a bit inconsistent between different databases. So double-check this if you are using SQLite or Postgresql. 

And if you would like to read about this by someone who appears to know how to code in Python: I found this blog post that I thought was pretty good and went into more details: https://www.btelligent.com/en/blog/best-practice-for-sql-statements-in-python/

---
Johannes B. Ullrich, Ph.D., Dean of Research, SANS Technology Institute
Twitter|