1

Im currently sending a twitter stream to a local MySQL db and I have run into an issue. Whenever a user creates a tweet with " located within it, I will get a syntax error because it messes with the Insert statement.

Im curious of the best way to fix this so a persons tweet will not effect the insert statement.

example variables:

tweetId = 98757629
userId = 07gos870sg
text = "this is "what " is messing up my sql"
day = 04
month = 'dec'
year = 2016
hour = 23
minute = 45
placeId = 'kj4h5b899'


c.execute('INSERT INTO tweet VALUES("%s", "%s", "%s", "%s", "%s", 
           "%s", "%s", "%s", "%s")' % \
           (tweetId, userId, text, day, month, year, hour, minute, placeId))

Iv thought of just taking any of the characters (" ` ') that would mess with he insert statement out before they are sent to the code, however; I dont want to edit any user submitted data.

salty_coffee
  • 631
  • 1
  • 10
  • 22
  • See the answers to the question [Escape string Python for MySQL](http://stackoverflow.com/questions/3617052/escape-string-python-for-mysql). – Wheat Dec 07 '16 at 22:53

1 Answers1

2

You should bind the variables instead of formatting the sql string.

sql = "INSERT INTO tweet VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
values = (tweetId, userId, text, day, month, year, hour, minute, placeId)
cursor.execute(sql, values)
Maurice Meyer
  • 17,279
  • 4
  • 30
  • 47