Python MySQLdb prevent SQL injection - not working as expected

Question

I am trying to query a MySQL database in a secure way, avoiding SQL injection. I am getting an error when trying to execute the SQL in the DB cursor.

My code looks like this:

reseller_list = ('138',)
for reseller in reseller_list:
  cur1 = db.cursor()

  dbQuery = """
  SELECT
    TRIM(CONCAT(TRIM(c1.first_name), ' ', TRIM(c1.name))) AS 'User name',
      FORMAT(sum(cost1),2) AS 'cost1',
      FORMAT(sum(cost2),2) AS 'cost2',
    FROM
      client as c1,
      client as c2
    WHERE
      c2.id = %s
      AND start BETWEEN DATE_FORMAT(CURRENT_DATE - INTERVAL 1 MONTH, '%Y-%m-01 00:00:00')
      AND DATE_FORMAT(LAST_DAY(CURRENT_DATE - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59')
    GROUP BY
      c1.name
    ORDER BY
      CONCAT(c1.first_name, ' ', c1.name);
  """

  cur1.execute(dbQuery, (reseller_id,))

And what happens is this:

  cur1.execute(dbQuery, (reseller_id,))
  File "/usr/lib64/python2.7/site-packages/MySQLdb/cursors.py", line 159, in execute
  query = query % db.literal(args)
  TypeError: not enough arguments for format string

I have read a number of pages both on this site and others but can't see what I am doing wrong. I can easily do this using string substitution into the query but want to do it the right way!

Daniel Roseman · Accepted Answer · 2016-02-09 18:38:20Z

1

You have % signs in your date_format calls, so you'll need to escape them from the param substitution by doubling them.

WHERE
  c2.id = %s
  AND start BETWEEN DATE_FORMAT(CURRENT_DATE - INTERVAL 1 MONTH, '%%Y-%%m-01 00:00:00')
  AND DATE_FORMAT(LAST_DAY(CURRENT_DATE - INTERVAL 1 MONTH), '%%Y-%%m-%%d 23:59:59')

answered Feb 9, 2016 at 18:38

Daniel Roseman

602k68 gold badges911 silver badges924 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python MySQLdb prevent SQL injection - not working as expected

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related