How to use .format() string in a MySQL statement?

Question

I'm trying to format a MySQL statement with today's data, as well as 7 days back. I'm pretty sure the date is in the correct format, so I don't think that's the issue.

The error report is:

 Warning: (1292, "Incorrect datetime value: '{} 16:00:00' for column 'run_start_date' at row 1")
      result = self._query(query)
    Traceback (most recent call last):
         AttributeError: 'int' object has no attribute 'format'

e.g.

today = DT.date.today()
week_ago = today - DT.timedelta(days=7)
print(today.strftime('%Y-%m-%d'))
print(week_ago.strftime('%Y-%m-%d'))

cursor.execute(SELECT * FROM db WHERE run_start_date BETWEEN '{} 16:00:00' AND '{} 16:00:00'format(week_ago, today)

The code you show is syntactically incorrect. What is your actual code? — cs95
– cs95, Commented Nov 15, 2017 at 23:17
Check my answer, hope it helped. If so please mark as correct. Best of luck! — alexisdevarennes
– alexisdevarennes, Commented Nov 15, 2017 at 23:31

alexisdevarennes · Accepted Answer · 2017-11-15 23:44:34Z

6

Using prepared statements (safe way):

qry = """
    SELECT *
    FROM db
    WHERE run_start_date BETWEEN '%s 16:00:00' AND '%s 16:00:00'
""" # 

today = DT.date.today()
week_ago = today - DT.timedelta(days=7)
today = str(today.strftime('%Y-%m-%d'))  # Convert to string
week_ago = str(week_ago.strftime('%Y-%m-%d'))  # Convert to string

cursor.execute(qry, [today, week_ago])

Using .format() which leaves you at risk for sql injections (if you pass user input to .format() e.g.)

qry = """
    SELECT *
    FROM db
    WHERE run_start_date BETWEEN '{today} 16:00:00' AND '{week_ago} 16:00:00'
""" # Use named placeholders, nicer to read, prevents you having to repeat variables multiple time when calling .format()

today = DT.date.today()
week_ago = today - DT.timedelta(days=7)
today = str(today.strftime('%Y-%m-%d'))  # Convert to string
week_ago = str(week_ago.strftime('%Y-%m-%d'))  # Convert to string

qry = qry.format(today=today, week_ago=week_ago)
cursor.execute(qry)

edited Nov 15, 2017 at 23:44

answered Nov 15, 2017 at 23:29

alexisdevarennes

5,6604 gold badges28 silver badges39 bronze badges

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

12 Comments

Shadow Over a year ago

Then why not show an example of that instead? Don't confused what the asker has said and what they want. It's more likely they want the equivalent in sql - which is prepared statements.

Shadow Over a year ago

It will happen when someone comes alone to the site and copy pastes this example for user input. There's no good reason to teach bad habits like this one, except to show why it's a bad habit.

Shadow Over a year ago

And good on you. +1 for the bold text, though I think you could also swap them around (ie, this is the way to do it correctly, and this is the dangerous way if you really want to know) as it'll help readers chose the correct one.

alexisdevarennes Over a year ago

You are right, really grateful for input, didnt consider fact other users might copy answer. High five bro/sis!

spencer7593 Over a year ago

Very nice. +10. In this particular case, the code wasn't subject to SQL Injection... but it did follow the same pattern we see in code that is vulnerable to SQL Injection. It's much better to do it the right way, with prepared statement and bind placeholder. It's much easier for the reader to see (right there where the SQL text is constructed) that it's not a pattern vulnerable to SQL Injection. And it's not hard to do. Exploits of a Mom xkcd.com/327 and OWASP SQL Injection owasp.org/index.php/SQL_Injection

|

Collectives™ on Stack Overflow

How to use .format() string in a MySQL statement?

1 Answer 1

Using prepared statements (safe way):

Using .format() which leaves you at risk for sql injections (if you pass user input to .format() e.g.)

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Using prepared statements (safe way):

Using .format() which leaves you at risk for sql injections (if you pass user input to .format() e.g.)

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related