how to format variables before db to avoid errors

Question

I am recieving errors like this one:

_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 't Stop.mp3' LIMIT 1' at line 1")

Because I am trying to compare a URL that exists in my DB to one in a variable before I choose to insert it or not with the below code:

`#see if any links in the DB match the crawled link

check_exists_sql = "SELECT * FROM LINKS WHERE link = '%s' LIMIT 1" % item['link'].encode("utf-8") 

cursor.execute(check_exists_sql)`

Obviously the ' character and perhaps other characters are causing problems.

How do I format these URLs to avoid this?

Answer 1

You need to escape any strings in your MySQL when you place these strings into an SQL statement.

Escaping will, among other things, make sure that apostrophes don't break your SQL and introduce security vulnerabilities.

Use mysql.escape_string() (where mysql is your database object)

Answer 2

Let the MySQLdb module do the interpolation:

cursor.execute("""SELECT * FROM LINKS WHERE link = %s LIMIT 1""",
    (item['link'].encode("utf-8"),)
)

The execute() function can be passed items to be substituted into the query (see the documentation for execute()). It will automatically escape things as necessary for the DB query.

If you prefer to use a dict instead of a tuple to specify the things to substitute in:

cursor.execute("""SELECT * FROM LINKS WHERE link = %(link)s LIMIT 1""",
    {'link': item['link'].encode("utf-8")}
)