MySQL parameterized queries

Question

I am having a hard time using the MySQLdb module to insert information into my database. I need to insert 6 variables into the table.

cursor.execute ("""
    INSERT INTO Songs (SongName, SongArtist, SongAlbum, SongGenre, SongLength, SongLocation)
    VALUES
        (var1, var2, var3, var4, var5, var6)

""")

Can someone help me with the syntax here?

score 280 · Accepted Answer · edited May 21 '13 at 15:41

280

Beware of using string interpolation for SQL queries, since it won't escape the input parameters correctly and will leave your application open to SQL injection vulnerabilities. The difference might seem trivial, but in reality it's huge.

Incorrect (with security issues)

c.execute("SELECT * FROM foo WHERE bar = %s AND baz = %s" % (param1, param2))

Correct (with escaping)

c.execute("SELECT * FROM foo WHERE bar = %s AND baz = %s", (param1, param2))

It adds to the confusion that the modifiers used to bind parameters in a SQL statement varies between different DB API implementations and that the mysql client library uses printf style syntax instead of the more commonly accepted '?' marker (used by eg. python-sqlite).

edited May 21 '13 at 15:41

Roman Podlinov

23,806
7
41
60

answered Apr 22 '09 at 01:35

Emil H

39,840
10
78
97

3

@Specto IMO it makes sense to always stay with right and secure implementation ways. It creates right habits and a good culture of programming. Also nobody knows how you code will be used in the future; somebody can use it later for other system or website. – Roman Podlinov May 21 '13 at 15:40
@BryanHunt You can turn on the use of ? with an argument somewhere, but it is discouraged because it doesn't tell you much about which argument goes where. (The same could be said of %s, of course, which is discouraged for the same reason.) More information here: https://www.python.org/dev/peps/pep-0249/#paramstyle – kqr Jan 15 '15 at 10:12
1

Coming from php/pdo I was mega-confused if the `printf` style `%s` marker, and I was sorta terrified that I was writing vulnerable queries. Thanks for clearing that worry up! :) – Darragh Enright Jun 28 '15 at 01:43
Do you have any docs that go through the difference between `%` and `,`? – bzupnick Apr 03 '16 at 16:39
@bzupnick The `%` operator puts variables into a string, this is general python code. The `,` simply separates arguments in a function/method call, so your variable list becomes a second argument. This `execute` method apparently has an optional second argument. – Luc May 13 '16 at 22:01
25

When it's a single parameter remember to keep the comma: `c.execute("SELECT * FROM foo WHERE bar = %s", (param1,))` – Marius Lian Jul 27 '16 at 20:31
2

For context on the parameters in the cursor execute (and why %s is still needed), the api reference for the MySQLdb cursor is at http://mysql-python.sourceforge.net/MySQLdb-1.2.2/public/MySQLdb.cursors.BaseCursor-class.html#execute – RedBarron Mar 16 '17 at 16:51
If I am not wrong this is also the way we insert into string of logger functions like info, error etc.... – Ciasto piekarz Feb 12 '18 at 10:47

Trey Stout · Answer 2 · 2009-04-22T02:20:57.710

You have a few options available. You'll want to get comfortable with python's string iterpolation. Which is a term you might have more success searching for in the future when you want to know stuff like this.

Better for queries:

some_dictionary_with_the_data = {
    'name': 'awesome song',
    'artist': 'some band',
    etc...
}
cursor.execute ("""
            INSERT INTO Songs (SongName, SongArtist, SongAlbum, SongGenre, SongLength, SongLocation)
            VALUES
                (%(name)s, %(artist)s, %(album)s, %(genre)s, %(length)s, %(location)s)

        """, some_dictionary_with_the_data)

Considering you probably have all of your data in an object or dictionary already, the second format will suit you better. Also it sucks to have to count "%s" appearances in a string when you have to come back and update this method in a year :)

Looks like the dictionary approach works better in case a given bind variable has to be used in more than one place in the SQL statement. With the positional approach, we need to pass the variable as many times as it is referenced, which is not very desirable. — codeforester, Dec 19 '18 at 00:14

score 15 · Answer 3 · answered Apr 22 '09 at 00:53

15

The linked docs give the following example:

   cursor.execute ("""
         UPDATE animal SET name = %s
         WHERE name = %s
       """, ("snake", "turtle"))
   print "Number of rows updated: %d" % cursor.rowcount

So you just need to adapt this to your own code - example:

cursor.execute ("""
            INSERT INTO Songs (SongName, SongArtist, SongAlbum, SongGenre, SongLength, SongLocation)
            VALUES
                (%s, %s, %s, %s, %s, %s)

        """, (var1, var2, var3, var4, var5, var6))

(If SongLength is numeric, you may need to use %d instead of %s).

answered Apr 22 '09 at 00:53

Marcel Guzman

1,672
1
10
6

1

will this work where the var1 and var2 have charecters like " or ' . – sheki Oct 24 '11 at 11:53
3

AFAIK you have to use `%s` there no matter which type. @sheki: Yes – ThiefMaster May 27 '16 at 11:03

score 7 · Answer 4 · edited May 13 '21 at 14:51

As an alternative to the chosen answer, and with the same safe semantics of Marcel's, here is a compact way of using a Python dictionary to specify the values. It has the benefit of being easy to modify as you add or remove columns to insert:

  meta_cols = ('SongName','SongArtist','SongAlbum','SongGenre')
  insert = 'insert into Songs ({0}) values ({1})'.format(
      ','.join(meta_cols), ','.join( ['%s']*len(meta_cols)))
  args = [ meta[i] for i in meta_cols ]
  cursor = db.cursor()
  cursor.execute(insert,args)
  db.commit()

Where meta is the dictionary holding the values to insert. Update can be done in the same way:

  meta_cols = ('SongName','SongArtist','SongAlbum','SongGenre')
  update='update Songs set {0} where id=%s'.
        .format(','.join([ '{0}=%s'.format(c) for c in meta_cols ]))
  args = [ meta[i] for i in meta_cols ]
  args.append(songid)
  cursor=db.cursor()
  cursor.execute(update,args)
  db.commit()

I am impressed ... you managed to make python code unreadable! — Iharob Al Asimi, Nov 30 '16 at 22:33

score 7 · Answer 5 · answered Mar 27 '11 at 09:31

Actually, even if your variable (SongLength) is numeric, you will still have to format it with %s in order to bind the parameter correctly. If you try to use %d, you will get an error. Here's a small excerpt from this link http://mysql-python.sourceforge.net/MySQLdb.html:

To perform a query, you first need a cursor, and then you can execute queries on it:

c=db.cursor()
max_price=5
c.execute("""SELECT spam, eggs, sausage FROM breakfast
          WHERE price < %s""", (max_price,))

In this example, max_price=5 Why, then, use %s in the string? Because MySQLdb will convert it to a SQL literal value, which is the string '5'. When it's finished, the query will actually say, "...WHERE price < 5".

Yeah this is a strange one alright ... you think that "printf" format would mean... actually printf format and not just using %s everywhere. — fthinker, Oct 10 '13 at 18:38

score 1 · Answer 6 · answered Sep 12 '18 at 21:24

The first solution works well. I want to add one small detail here. Make sure the variable you are trying to replace/update it will has to be a type str. My mysql type is decimal but I had to make the parameter variable as str to be able to execute the query.

temp = "100"
myCursor.execute("UPDATE testDB.UPS SET netAmount = %s WHERE auditSysNum = '42452'",(temp,))
myCursor.execute(var)

score 0 · Answer 7 · answered Feb 01 '18 at 00:04

Here is another way to do it. It's documented on the MySQL official website. https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html

In the spirit, it's using the same mechanic of @Trey Stout's answer. However, I find this one prettier and more readable.

insert_stmt = (
  "INSERT INTO employees (emp_no, first_name, last_name, hire_date) "
  "VALUES (%s, %s, %s, %s)"
)
data = (2, 'Jane', 'Doe', datetime.date(2012, 3, 23))
cursor.execute(insert_stmt, data)

And to better illustrate any need for variables:

NB: note the escape being done.

employee_id = 2
first_name = "Jane"
last_name = "Doe"

insert_stmt = (
  "INSERT INTO employees (emp_no, first_name, last_name, hire_date) "
  "VALUES (%s, %s, %s, %s)"
)
data = (employee_id, conn.escape_string(first_name), conn.escape_string(last_name), datetime.date(2012, 3, 23))
cursor.execute(insert_stmt, data)

MySQL parameterized queries

7 Answers7

Incorrect (with security issues)

Correct (with escaping)

Linked

Related