OpenStack Security Advisory

MySQLdb escaping example incorrect

Bug #1745555 reported by Joshua Padman on 2018-01-26

This bug affects 1 person

Affects		Status	Importance	Assigned to	Milestone
	OpenStack Security Advisory	Won't Fix	Undecided	Unassigned
	OpenStack Security Notes	New	Low	Joshua Padman

Bug Description

The following web page provides information for securing sql queries using parameterized queries.

https://security.openstack.org/guidelines/dg_parameterize-database-queries.html

The first "correct" example under MySQL is incorrect. Firstly, it escapes the entire query which would lead to it being invalid SQL. Secondly, it isn't a real parameterised query.

import MySQLdb

query = "select username from users where username = '%s'" % name
con = MySQLdb.connect('localhost', 'testuser', 'test623', 'testdb');

with con:
cur = con.cursor()
cur.execute(MySQLdb.escape_string(query)) <-- escaping the whole query will lead to the ' around the name being escaped.

There are three ways I can see to fix this, my preference is 2:
1. Modify the example as follows - escaping the user input only:
import MySQLdb

query = "select username from users where username = '%s'" % MySQLdb.escape_string(name)
con = MySQLdb.connect('localhost', 'testuser', 'test623', 'testdb');

with con:
cur = con.cursor()
cur.execute(query)

2. Do step option 1, plus note that this is still not ideal and that the second example is the actual "correct" parameterised query example.

3. Remove this example from the documentation.

This was bought to my attention by a friend sneakerhax.