Retrieving and selecting binary values from Mysql with Python 3

Question

I'm trying to select data from one table, and perform a query on another table using the returned values from the first table.

Both tables are case-sensitive, and of type utf8-bin.

When I perform my first select, I am returned a tuple of binary values:

query = """SELECT id FROM table1"""
results = (b'1234', b'2345', b'3456')

I'd then like to perform a query on table2 using the ids returned from table1:

query = """SELECT element FROM table2 WHERE id IN (%s) """ % results

Is this the right way to do this?

Absolutely not, your values added with % are not escaped and is not guaranteed to work (not to mention the injection vulnerability). — ohmu
– ohmu, Commented Jul 8, 2014 at 14:34
Mysql.connector. I tried parameterizing the query but I get an error that I can't parameterize a tuple. Trying to find a way to do these 2 queries without processing the data inbetween. Thanks — ensnare
– ensnare, Commented Jul 8, 2014 at 14:38

ohmu · Accepted Answer · 2014-07-08 14:43:29Z

1

You need to create the query so that it can be properly parameterized:

query = """SELECT element FROM table2 WHERE id IN (%s) """ % ",".join(['%s'] * len(results))

This will transform the query to:

query = """SELECT element FROM table2 WHERE id IN (%s,%s,%s) """

Then you can just pass query and results to the execute() (or appropriate) method so that results are properly parameterized.

answered Jul 8, 2014 at 14:43

ohmu

19.9k44 gold badges114 silver badges151 bronze badges

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

2 Comments

ensnare Over a year ago

thanks, this works. is there any performance downside to doing this?

ohmu Over a year ago

@ensnare A performance downside you could encounter would be if you had 1000+ IDs because the query would have id IN (%s, %s, ... repeated 1000+ times) which could be large, but that could be solved with batching if it is an issue.

Collectives™ on Stack Overflow

Retrieving and selecting binary values from Mysql with Python 3

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related