Search code examples
pythonsecuritycode-injectionpsqlpgdb

Does pgdb prevent against injection attacks?

I have a piece of code like this:

db = pgdb.connect(
    database=connection['database'],
    user=connection['user'],
    host=connection['host'])
cursor = db.cursor()
# ask database
query = '''
    SELECT a, b, c
    FROM table
    WHERE a ILIKE %s;'''
try:
    cursor.execute(query, userInput)
except pgdb.Error, error:
    error = str(error)
    print json.dumps({
        'errorMessage': 'ERROR: %s' % error
    })

I have read in another forum that python modules like MySQLdb do escaping to prevent against injection attacks. I have also looked through the documentation on pgdb but it is pretty thin. Lastly, I tried to do my own injection attacks using my own test database, but I'm not sure if my tests are sufficient. What would be a good way to test this out?

Solution

  • All DB-API modules protect against SQL injection when you use the execute method with all variable input kept in the parameter list (userInput in your example, which is safe).

    It turns out that for pgdb the way it does this is indeed by escaping each of the parameters to get SQL literal values before injecting them into the placeholders in the SQL query. That needn't necessarily be the case: some database connectors can pass parameters to their server as separate structures rather than part of the query string, and there are potentially performance benefits from doing that. Ultimately though you shouldn't really care what method is being used - you deliver the parameters separately to the DB-API connector, and it is reponsible for making that work in a secure way.

    Of course if you start dropping variables into the query yourself instead (eg "WHERE a ILIKE '%s'" % userInput), pgdb or any other connector can't stop you from hurting yourself.