Phelio Gnomi

My words from my mind

Tag Archives: sql injection

Reduce SQL Injection Risk in Python and psycopg2

It will be surprising that a slight different in your line of code can have a great impact in preventing SQL injection. Try to spot the difference between these 2 lines below.

# code 1 (with python string format() function)
db.execute("select * from some_table where filter = '{}'".format("going to Sam's party"))
# code 2 (with psycopg2 sql prepare syntax)
db.execute("select * from some_table where filter = %s", ("going to Sam's party"))

It probably looks obviously different, but for a second it looks like it shouldn’t give much different result. But sadly it does.

The first code use the python standard string formatting feature where given a string containing curly brackets as a placeholders like “This is a {}”, and with the format() method, it will fill those placeholders with other strings.

# Example
sample1 = "this is a {}, and also a {}".format("pen", "weapon")
print(sample1)
 
#results
# this is a pen, and also a weapon 

this looks fine for now. But try to do one for the string that we pass into the db.execute() above. If we print the string, it will give you the result below

select * from some_table where filter = 'going to Sam's party'

Notice the extra single quote on the filter? This will cause error and of course opening a whole world of opportunity for SQL injection attack. With the single quote unescaped, the rest of the string can be injected with other commands that will cause serious maintenance headache.

-- example: imagine the replacer string is "bleh'; drop table some_table; insert into user values ('some new malicious users'); --"
-- your query will become
select * from some_table where filter = 'bleh'; drop table some_table; insert into user values ('some new malicious users'); --'
-- note that double dash (--) is used for commenting. So the extra single quote will be ignored.

So, why does code 2 is a better way of string replacements? Because it has built in special character escaping mechanism in which all strings that are passed thorough this method will remain as a string instead of becoming a malicious codes.

db.execute("select * from some_table where filter = %s", ("bleh'; drop table some_table; insert into user values ('some new malicious users'); --"))
the code above will produce sql below

 

select * from some_table where filter = E'bleh\'; drop table some_table; insert into user values (\'some new malicious users\'); --'
Advertisements