-1

So currently my code is this which works 

result = connection.execute('SELECT * FROM PastOrders WHERE CustomerID="1";')

But as others use my programme the customer will change so how do I replace 1 with a variable like this..

cat = str(1)    
result = connection.execute('SELECT * FROM PastOrders WHERE CustomerID=cat;')

However, this doesn't work so any help thanks

Megan
  • 50
  • 8
  • 1
    The [sqlite3 documentation](https://docs.python.org/3/library/sqlite3.html) covers this for you; look for *placeholders* and *parameters*. Do not use string interpolation, as that would open you up to SQL injection attacks! – Martijn Pieters Nov 13 '18 at 09:53
  • For this specific query, use one placeholder (`?`): `result = connection.execute('SELECT * FROM PastOrders WHERE CustomerID=?;', (cat,))`, there `cat` is the customer ID. You do not need to convert it to a string first. – Martijn Pieters Nov 13 '18 at 10:00

1 Answers1

0

Have you considered breaking the SQL statement.

cat = '\"+'str(1)+'\"';
result = connection.execute('SELECT * FROM PastOrders WHERE CustomerID='+cat+';)'
Matnex Mix
  • 49
  • 8
  • 1
    ***NO***, you should ***NEVER*** use string formatting to interpolate untrusted data into a SQL command! – Martijn Pieters Nov 13 '18 at 09:57
  • 1
    See the [OWASP SQL Injection overview](https://www.owasp.org/index.php/SQL_Injection) for why this is a **really bad idea**. – Martijn Pieters Nov 13 '18 at 09:59
  • He should add some security measures. Converting html special characters and SQL keywords. – Matnex Mix Nov 13 '18 at 10:00
  • 1
    Yes, and the first security measure is to *use SQL parameters and not use string formatting*. – Martijn Pieters Nov 13 '18 at 10:01
  • 1
    From the `sqlite3` documentation: *Instead, use the DB-API’s parameter substitution. Put `?` as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s `execute()` method.*. SQL parameters take care of proper escaping of values for you. – Martijn Pieters Nov 13 '18 at 10:05
  • How to do that, I don't think I've ever heard of it. – Matnex Mix Nov 13 '18 at 10:06