Executing "SELECT ... WHERE ... IN ..." using MySQLdb

Question

I'm having a problem executing some SQL from within Python, despite similar SQL working fine from the mysql command-line.

The table looks like this:

mysql> SELECT * FROM foo;
+-------+-----+
| fooid | bar |
+-------+-----+
|     1 | A   | 
|     2 | B   | 
|     3 | C   | 
|     4 | D   | 
+-------+-----+
4 rows in set (0.00 sec)

I can execute the following SQL query from the mysql command-line, without a problem:

mysql> SELECT fooid FROM foo WHERE bar IN ('A','C');
SELECT fooid FROM foo WHERE bar IN ('A','C');
+-------+
| fooid |
+-------+
|     1 | 
|     3 | 
+-------+
2 rows in set (0.00 sec)

However, when I try to do the same from within Python, I get no rows, while I expected 2 rows:

import MySQLdb
import config
connection=MySQLdb.connect(
    host=config.HOST,user=config.USER,passwd=config.PASS,db='test')
cursor=connection.cursor()

sql='SELECT fooid FROM foo WHERE bar IN %s'
args=[['A','C']]
cursor.execute(sql,args)
data=cursor.fetchall()
print(data)
# ()

So the question is: how should the python code be modified to select those fooids where bar is in ('A','C')?

By the way, I noticed that if I switch the roles of bar and fooid, I can get the code to select those bars where fooid is in (1,3) successfully. I don't understand why one such query (below) works, while the other one (above) doesn't.

sql='SELECT bar FROM foo WHERE fooid IN %s'
args=[[1,3]]
cursor.execute(sql,args)
data=cursor.fetchall()
print(data)
# (('A',), ('C',))

And just to be absolutely clear, this is how the foo table was created:

mysql> DROP TABLE IF EXISTS foo;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `foo` (
          `fooid` int(11) NOT NULL AUTO_INCREMENT,
          `bar` varchar(10) NOT NULL,
          PRIMARY KEY (`fooid`));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT into foo (bar) values ('A'),('B'),('C'),('D');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

Edit: When I enable the general query log with mysqld -l /tmp/myquery.log I see

mysqld, Version: 5.1.37-1ubuntu5.5-log ((Ubuntu)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
110101 11:45:41     1 Connect   unutbu@localhost on test
            1 Query set autocommit=0
            1 Query SELECT fooid FROM foo WHERE bar IN ("'A'", "'C'")
            1 Query SELECT bar FROM foo WHERE fooid IN ('1', '3')
            1 Quit

Indeed, it looks like too many quotes are being placed around A and C.

Thanks to @Amber's comment, I understand better what is going wrong. MySQLdb converts the parametrized argument ['A','C'] to ("'A'","'C'").

Is there a way to make a parametrized query using the IN SQL syntax? Or must one manually construct the SQL string?

Can you look in your MySQL server's logs and find out what query was actually run? It seems to have something to do with how strings/varchars are being interpreted (since python int -> mysql int works fine). — Amber, Jan 01 '11 at 16:10

score 73 · Answer 1 · edited Oct 02 '14 at 11:45

73

Unfortunately, you need to manually construct the query parameters, because as far as I know, there is no built-in bind method for binding a list to an IN clause, similar to Hibernate's setParameterList(). However, you can accomplish the same with the following:

Python 3:

args=['A', 'C']
sql='SELECT fooid FROM foo WHERE bar IN (%s)' 
in_p=', '.join(list(map(lambda x: '%s', args)))
sql = sql % in_p
cursor.execute(sql, args)

Python 2:

args=['A', 'C']
sql='SELECT fooid FROM foo WHERE bar IN (%s)' 
in_p=', '.join(map(lambda x: '%s', args))
sql = sql % in_p
cursor.execute(sql, args)

edited Oct 02 '14 at 11:45

answered Jan 01 '11 at 16:13

João Silva

89,303
29
152
158

Thanks for the idea, but I'm having trouble making it work. I get: _mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''(\\'A\\', \\'C\\')'' at line 1"). – unutbu Jan 01 '11 at 16:30
You are correct indeed. I first misread the question, and thought you wanted to use a list of `int`, where you wouldn't have any escape-related issue. Can you please test if the updated solution works? – João Silva Jan 01 '11 at 16:51
@João: please note you need to put `args` instead of `l` in your in_p= line, and it works correctly only for numeric arguments since it doesn't put apices around strings – Luke404 Dec 06 '11 at 10:09
3

`in_p = ', '.join(itertools.repeat('%s', len(args)))` – dhill Dec 07 '12 at 00:53
17

This would be prone to sql injection. Isn't there something safer that we can do? – Sohaib Sep 16 '14 at 04:29
9

@Sohaib how would this be prone to injection? The data being used for the query (args) is passed separate from the sql text. The string manipulation only adds more placeholders '%s', not the actual data which would be vulnerable. – Despertar Jun 02 '16 at 20:01
5

`in_p = ', '.join(['%s']*len(args))`, while we are at it… – Oct 04 '16 at 14:10
`in_p = ('%s,' * len(args))[:-1]` will faster – consatan Mar 28 '17 at 16:49
2

Have SQL Inyection vulnerability, the arguments is not escaped in sql secuence. mysql.exec use the query string and arguments, the arguments are escaped, but with a manualy join it is not escaped. Example: adding into argument a double quote, backslash, newlines, wildcards, backspaces, etc, problems with charsets, by example, define the connection in utf8 but the query is escaped in japan text codification, etc. https://es.slideshare.net/openpbs/sql-injection-defense-in-python . How many people have implemented this solution in violation of their developments? – e-info128 Apr 12 '17 at 03:39
2

Anyone who downvotes because of SQL injection vulnerability - you are doing mistake. This answer does not contain this vulnerability, even in previous edits and the initial answer. Please read the answer carefully. The arguments for the query are properly passed to `execute`, all the formatting is needed to have proper count of initial `%s` occurrences in the `IN` clause . – viddik13 Oct 31 '17 at 11:25

score 55 · Accepted Answer · edited May 23 '17 at 12:10

55

Here is a similar solution which I think is more efficient in building up the list of %s strings in the SQL:

Use the list_of_ids directly:
format_strings = ','.join(['%s'] * len(list_of_ids))
cursor.execute("DELETE FROM foo.bar WHERE baz IN (%s)" % format_strings,
                tuple(list_of_ids))
That way you avoid having to quote yourself, and avoid all kinds of sql injection.

Note that the data (list_of_ids) is going directly to mysql's driver, as a parameter (not in the query text) so there is no injection. You can leave any chars you want in the string, no need to remove or quote chars.

edited May 23 '17 at 12:10

Community

1
1

answered May 02 '12 at 09:12

Robert Bergs

682
6
8

2

I like this, I would go as far as saying that the accepted answer is to be considered harmful! – frankster Jun 14 '15 at 11:51
3

Agreed - this is a much better solution. The above opens you up to SQL injection attacks, for example if a user types `); select username, password, credit_card from user;` their code could be run against the database. – Chris Feb 13 '16 at 18:35

score 11 · Answer 3 · answered Sep 09 '11 at 10:39

If you have other parameters in the query, beyond the IN list, then the following extension to JG's answer may be useful.

ids = [1, 5, 7, 213]
sql = "select * from person where type=%s and id in (%s)"
in_ids = ', '.join(map(lambda x: '%s', ids))
sql = sql % ('%s', in_ids)
params = []
params.append(type)
params.extend(ids)
cursor.execute(sql, tuple(params))

That is, join all the params in a linear array, then pass it as a tuple to the execute method.

score 6 · Answer 4 · answered Jan 17 '14 at 07:15

6

this works for me:

myTuple= tuple(myList)
sql="select fooid from foo where bar in "+str(myTuple)
cursor.execute(sql)

answered Jan 17 '14 at 07:15

shanal

105
3
9

4

Never pass parameters directly in the SQL query! This leads to SQL injection vulnerabilities which is a major security issue. Parameters should always be passed in the parameter tuple so they get properly quoted by the SQL binding library. Different engines have different rules for quoting, and the only safe way to quote parameters is to let the lower-level library do it. – Thomas Guyot-Sionnest Oct 22 '16 at 18:28

score 2 · Answer 5 · answered Sep 26 '12 at 04:16

Have been trying every variation on João's solution to get an IN List query to work with Tornado's mysql wrapper, and was still getting the accursed "TypeError: not enough arguments for format string" error. Turns out adding "*" to the list var "*args" did the trick.

args=['A', 'C']
sql='SELECT fooid FROM foo WHERE bar IN (%s)'
in_p=', '.join(list(map(lambda x: '%s', args)))
sql = sql % in_p
db.query(sql, *args)

score 2 · Answer 6 · answered Oct 08 '13 at 17:39

Improving on João's and satru's code, I suggest creating a cursor mixin that can be used to build a cursor with an execute that accepts nested iterables and handles them correctly. A better name would be nice, though... For Python3, use str instead of basestring.

from MySQLdb.cursors import Cursor

class BetterExecuteMixin(object):
    """
    This mixin class provides an implementation of the execute method
    that properly handles sequence arguments for use with IN tests.
    Examples:
    execute('SELECT * FROM foo WHERE id IN (%s) AND type=%s', ([1,2,3], 'bar'))
    # Notice that when the sequence is the only argument, you still need
    # a surrounding tuple:
    execute('SELECT * FROM foo WHERE id IN (%s)', ([1,2,3],))
    """

    def execute(self, query, args=None):
        if args is not None:
            try:
                iter(args)
            except TypeError:
                args = (args,)
            else:
                if isinstance(args, basestring):
                    args = (args,)
            real_params = []
            placeholders = []
            for arg in args:
                # sequences that we treat as a single argument
                if isinstance(arg, basestring):
                    real_params.append(arg)
                    placeholders.append('%s')
                    continue
                try:
                    real_params.extend(arg)
                    placeholders.append(','.join(['%s']*len(arg)))
                except TypeError:
                    real_params.append(arg)
                    placeholders.append('%s')
            args = real_params
            query = query % tuple(placeholders)
        return super(BetterExecuteMixin, self).execute(query, args)

class BetterCursor(BetterExecuteMixin, Cursor):
    pass

This can then be used as follows (and it's still backwards compatible!):

import MySQLdb
conn = MySQLdb.connect(user='user', passwd='pass', db='dbname', host='host',
                       cursorclass=BetterCursor)
cursor = conn.cursor()
cursor.execute('SELECT * FROM foo WHERE id IN (%s) AND type=%s', ([1,2,3], 'bar'))
cursor.execute('SELECT * FROM foo WHERE id IN (%s)', ([1,2,3],))
cursor.execute('SELECT * FROM foo WHERE type IN (%s)', (['bar', 'moo'],))
cursor.execute('SELECT * FROM foo WHERE type=%s', 'bar')
cursor.execute('SELECT * FROM foo WHERE type=%s', ('bar',))

score 2 · Answer 7 · edited Jun 20 '20 at 09:12

2

Very simple:

Just use the below formation###

rules_id = ["9","10"]

sql2 = "SELECT * FROM attendance_rules_staff WHERE id in"+str(tuple(rules_id))

note the str(tuple(rules_id)).

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 20 '17 at 08:45

Mizanur Rahman

11
3

1

An elegant solution as long as `rules_id` never comes from an external source. If anyone outside your system can set the values for `rules_id`, your database will be vulnerable to SQL injection. – Jesse Hogan Jul 08 '18 at 14:58

score 2 · Answer 8 · answered Sep 29 '11 at 19:32

Maybe we can create a function to do what João proposed? Something like:

def cursor_exec(cursor, query, params):
    expansion_params= []
    real_params = []
    for p in params:
       if isinstance(p, (tuple, list)):
         real_params.extend(p)
         expansion_params.append( ("%s,"*len(p))[:-1] )
       else:
         real_params.append(p)
         expansion_params.append("%s")
    real_query = query % expansion_params
    cursor.execute(real_query, real_params)

score 0 · Answer 9 · answered Feb 16 '11 at 23:12

0

Why not just this in that case?

args = ['A', 'C']
sql = 'SELECT fooid FROM foo WHERE bar IN (%s)' 
in_p  =', '.join(list(map(lambda arg:  "'%s'" % arg, args)))
sql = sql % in_p
cursor.execute(sql)

results in:

SELECT fooid FROM foo WHERE bar IN ('A', 'C')

answered Feb 16 '11 at 23:12

michael

27
1

1

I think I answered my own question: If you pass the args separately, they will be properly escaped by the Mysqldb lib. – michael Feb 16 '11 at 23:22
1

Never pass parameters directly in the SQL query! This leads to SQL injection vulnerabilities which is a major security issue. Parameters should always be passed in the parameter tuple so they get properly quoted by the SQL binding library. Different engines have different rules for quoting, and the only safe way to quote parameters is to let the lower-level library do it. – Thomas Guyot-Sionnest Oct 22 '16 at 18:29

score -1 · Answer 10 · edited Mar 05 '18 at 08:47

-1

args should be tuple.

eg:

args = ('A','B')

args = ('A',) # in case of single

edited Mar 05 '18 at 08:47

Derlin

9,572
2
32
53

answered Jan 03 '17 at 12:58

aman2357

29
3

Executing "SELECT ... WHERE ... IN ..." using MySQLdb

10 Answers10

Very simple:

Linked