CODEWITHSUNDEEP
pythonmysql
btongeorge
btongeorge

Reputation: 453

Python MySQLdb prevent SQL injection - not working as expected

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!

Upvotes: 0

Views: 118

Answers (1)

Daniel Roseman
Daniel Roseman

Reputation: 599630

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')

Upvotes: 1

Related Questions