Python，MySQL _mysql_exceptions.ProgrammingError:(1064，'您的SQL语法有错误 [英] Python, MySQL _mysql_exceptions.ProgrammingError: (1064, 'You have an error in your SQL syntax

问题描述

我当前正在将Twitter流发送到本地MySQL数据库，并且遇到了问题.每当用户在其中创建带有"的推文时，我都会收到语法错误，因为它会与Insert语句混淆.

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.

示例变量:

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.

推荐答案

您应该绑定变量，而不要格式化sql字符串.

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)

这篇关于Python，MySQL _mysql_exceptions.ProgrammingError:(1064，'您的SQL语法有错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！