SQL error with sqlite in python

Question

i have a hopefully simple Problem with an SQL-command

Code:

c.execute("SELECT MAX(pic_num) FROM Pictures WHERE id = "+str(user_id))

pic_num is a column in the database and user_id is an Integer in the database

I thought everything would be right but i get this Error:

sqlite3.OperationalError: near ")": syntax error

this Information doesn't help me at all

You should print the constructed query string as it is fed to execute(). Maybe user_id isn't what you expect. — Michael Butscher
– Michael Butscher, Commented Feb 15, 2018 at 13:28
Just to add, string concatenation when executing SQL queries is BAD and leaves you wide open for SQL injection. Check this page: docs.python.org/2/library/sqlite3.html. Most drivers allow you to pass args for string replacement which will sanitize first and prevent said SQL injection. — wholevinski
– wholevinski, Commented Feb 15, 2018 at 13:28
@brunodesthuilliers - Based on OP question, I do not believe he's ready to deal with SQL Injection. — PM 77-1
– PM 77-1, Commented Feb 15, 2018 at 13:28
I think the docs suggest this should be something like: c.execute("SELECT MAX(pic_num) FROM Pictures WHERE id = ?", str(user_id)) — Stev
– Stev, Commented Feb 15, 2018 at 13:29

bruno desthuilliers · Accepted Answer · 2018-02-15 13:31:17Z

2

The correct way to use python's db-api is to use placeholders in your SQL query and pass query values along, ie:

c.execute("SELECT MAX(pic_num) FROM Pictures WHERE id=?", [user_id,])

Note that this might not necessarily solve your problem but since you didn't post the schema nor the user_id value we can't try & reproduce the issue.

answered Feb 15, 2018 at 13:31

bruno desthuilliers

78.3k6 gold badges102 silver badges129 bronze badges

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

Comments

AnythingIsFine · Accepted Answer · 2018-02-15 13:31:26Z

2

You should python sqlite module's substitution instead like so:

c.execute("SELECT MAX(pic_num) FROM Pictures WHERE id = ?", (user_id, ))

answered Feb 15, 2018 at 13:31

AnythingIsFine

1,81516 silver badges11 bronze badges

Comments

PM 77-1 · Accepted Answer · 2018-02-15 14:23:34Z

1

Thank you all for the fast answers!

c.execute("SELECT MAX(pic_num) FROM Pictures WHERE id = ?", (str(user_id), ))

this finally worked :)

I already have written some libs which should handle SQL-injection (they test the Input for quotes but you're right im very new with SQL :D)

edited Feb 15, 2018 at 14:23

PM 77-1

13.4k21 gold badges73 silver badges118 bronze badges

answered Feb 15, 2018 at 13:42

user8072518

214 bronze badges

1 Comment

Guy Over a year ago

Escaping the strings is worse than using place holders. The place holders don’t parse what they’re given as sql at all, it’s a single item.

Collectives™ on Stack Overflow

SQL error with sqlite in python

3 Answers 3

Comments

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Related