When i trying execute sql query appear error

Question

Even so how to solve this problem:

cursor.execute("""SELECT * FROM Users AS t1
                          INNER JOIN Users_has_Users AS t ON t.Users_id = t1.id
                          INNER JOIN Users AS t2 ON t.Users_id1 = t2.id
                          WHERE t1.email = %s AND t1.id != t2.id AND t2.id >= %s
                          ORDER BY t2.name {}
                          LIMIT 10""".format(order), (email, since_id, limit))

Error:

not all arguments converted during string formatting

Martijn Pieters · Accepted Answer · 2014-03-22 19:33:33Z

1

You cannot use SQL parameters to interpolate anything other than data; you cannot use it for any SQL keywords such as ASC, nor the limit parameter. That is the point of SQL parameters; to avoid their values from being interpreted as SQL instead.

Use string formatting to interpolate your sort direction and query limit instead:

cursor.execute("""SELECT * FROM Users AS t1
                  INNER JOIN Users_has_Users AS t ON t.Users_id = t1.id
                  INNER JOIN Users AS t2 ON t.Users_id1 = t2.id
                  WHERE t1.email = %s AND t1.id != t2.id AND t2.id >= %s
                  ORDER BY t2.name {}
                  LIMIT {}""".format(order, limit), (email, since_id))

This does assume that you have full control over the contents of order and limit; never set it from user-supplied data as string formatting like this would open you up to a SQL injection attack otherwise.

edited Mar 22, 2014 at 19:33

answered Mar 22, 2014 at 19:06

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

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

8 Comments

sberry Over a year ago

It should be conveyed to the OP that using the order and/or limit like this must be done with care to avoid potential sql injection from unsanitized data.

Martijn Pieters Over a year ago

@sberry: Indeed; I've altered the post since the LIMIT is a literal so as long as limit is an integer it should work (depending on the database adapter).

AlexO Over a year ago

New problem: not all arguments converted during string formatting

Martijn Pieters Over a year ago

@user3037421: My mistake; I used ? instead of %s for your limit parameter there (different database engines use different SQL parameter placeholders).

AlexO Over a year ago

And new error: (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 ''10'' at line 6")

|

Collectives™ on Stack Overflow

When i trying execute sql query appear error

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related