How to check and perform SQL statements in Python

Question

I am using the MySQLdb in Python. I was told that to properly create SQL statements and to avoid SQL injection attacks I should code something like:

sql = "insert into table VALUES ( %s, %s, %s )"
args = var1, var2, var3
cursor.execute( sql, args )

Or:

cursor.execute( "insert into table VALUES ( %s, %s, %s )", var1, var2, var3 )

Or even this (this might be wrong):

header = ( 'id', 'first_name', 'last_name' )
values = ( '12', 'bob'       , 'smith'     )
cursor.execute( "insert into table ( %s ) values ( %s )", ( header + values ) )

When I programmed in PHP, I would normally store my entire SQL statement as a long string then execute the string. Something like (with PostgreSQL):

$id         = db_prep_string( pg_escape_string( $id         ) );
$first_name = db_prep_string( pg_escape_string( $first_name ) );
$last_name  = db_prep_string( pg_escape_string( $last_name  ) );

$query = "insert into table ( id, first_name, last_name ) values ( $id, $first_name, $last_name )"

$result = pg_query( $con, $query );
$retval = pg_fetch_array( $result );

where db_prep_string is

function db_prep_string( $value ) {
    if( 
        !isset( $value ) ||
        (is_null($value)) ||
        preg_match( '/^\s+$/', $value ) ||
        ( $value == '' )
     ) {
    $value = 'null';
}
else {
    $value = "'$value'";
}
    return $value;
}

Then to debug, I could simply echo out $query to view the entire SQL statement. Is there something similar I can do with Python? In other words, can I store the safe SQL statement as a string, print it to debug, then execute it?

I would like to do something like:

id         = prevent_sql_injection_string( id         )
first_name = prevent_sql_injection_string( first_name )
last_name  = prevent_sql_injection_string( last_name  )

sql  = "insert into table ( id, first_name, last_name ) values "
sql += id         + ", "
sql += first_name + ", "
sql += last_name

print sql #for debugging

cursor.execute( sql )

If not, then how do I view the SQL statement in its entirety in Python?

Bonus question: how do I enter null values in a SQL statement using Python?

You embedded arguments in pgsql queries to get one huge string? Yuck! Even in PHP you have `pg_query_params` where you can separate the query from the values. — ThiefMaster, Jan 15 '12 at 23:38
I originally thought that I liked storing a SQL query as a long string so I can take that string and manually enter it as a command on mysql. After reading some of the comments and answers, it seems like I should be using parameterized queries with tuples and , symbol (excuse my vague descriptions). I don't mind doing that but then what is the best practice to debug the SQL query? — hobbes3, Jan 15 '12 at 23:50
The `_last_executed` cursor property looks promising, as mentioned in this SO answer: http://stackoverflow.com/a/7190914/4970 — James Murty, Jan 16 '12 at 00:11
@JamesMurty Cool! `cur._last_executed` seems very useful! I put it inside a `try` `except` block and it at least shows me how I am entering the wrong SQL query. Thanks a lot! — hobbes3, Jan 16 '12 at 00:35

srgerg · Accepted Answer · 2012-01-15T23:46:37.280

Have a look at the Python Database API specification as it answers some of your questions. For example, on NULL values the API specification says this:

SQL NULL values are represented by the Python None singleton on input and output.

and on parameter styles the specification offers multiple options to the implementer, including the 'format' option that MySQLdb uses.

If you want to see what query MySQLdb will execute against your database you can cheat a little and use the connections literal(...) method. For example:

>>> import MySQLdb
>>> db = MySQLdb.connect(...)
>>> print "insert into table VALUES ( %s, %s, %s )" % db.literal((5, "ab'c", None))
insert into table VALUES ( 5, 'ab\\'c', NULL )

However the db.literal(...) method isn't really meant to be used by clients of the MySQLdb module, so don't put it in production code.

Also, note that the MySQLdb documentation has this to say about parameters:

Parameter placeholders can only be used to insert column values. They can not be used for other parts of SQL, such as table names, statements, etc.

So beware of using the parameter placeholders for the column names of a table in an insert statement.

mathematical.coffee · Answer 2 · 2012-01-15T23:44:08.047

-3

The code that you would like to do is valid. You can do something like:

def prevent_sql_injection_string( value ):
    if (value is None or str(value).strip()=''):
        value = 'null'
    else:
        value = "'%s'" % str(value)
    return value    

# convert inputs
id         = prevent_sql_injection_string( id         )
first_name = prevent_sql_injection_string( first_name )
last_name  = prevent_sql_injection_string( last_name  )

# construct query
# Note: "some formatted string" % (value,value,value) syntax is like doing
# printf("some formatted string",value,value,value)
sql = "insert into table (id, first_name, last_name ) values (%s, %s, %s)" 
         % (id,first_name,last_name)

# print
print sql

# execute
cursor.execute(sql)

edited Jan 15 '12 at 23:44

answered Jan 15 '12 at 23:35

mathematical.coffee

55,977
11
154
194

Nobody should do that though! – ThiefMaster Jan 15 '12 at 23:38
Your code formatting seems wrong (no indented block after the first line that ends with `:`). Also, I don't see how this prevents anything. What if the string I'm protecting has a single quote in it? All you're doing is adding single quotes, so if my value is `"'; drop table foo"`, won't that cause problems? – Bryan Oakley Jan 15 '12 at 23:41
Please explain why not if you're going to downvote - I am curious and would honestly like to know. – mathematical.coffee Jan 15 '12 at 23:42
Thanks @BryanOakley, forgot about the extra line in there - fixed (and I just translated OP's code to Python - does PHP `pg_escape_string` guard against the `"';drop table foo"` sort of thing either?) – mathematical.coffee Jan 15 '12 at 23:43

How to check and perform SQL statements in Python

2 Answers2