Brian Peach Brian Peach - 2 years ago 121
SQL Question

Mysql Connector error 1064 - Update with URL

I am working on a twitter app in python and I am trying to update a user record in my database. I can insert fine but updating presents the following error:

mysql.connector.errors.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 '://
735726715267679398/m%HAWxt7_normal.jpg WHERE use' at line 1

Below is the code I am using for the UPDATE:

cursor.execute("UPDATE user_table SET following='" +
str(all_data['user']['following']) + "', followers_count=" +
str(all_data['user']['followers_count']) + ", favourites_count=" +
str(all_data['user']['favourites_count']) + ", friends_count=" +
str(all_data['user']['friends_count']) + ", statuses_count=" +
str(all_data['user']['statuses_count']) + ", verified=" +
str(all_data['user']['verified']) + ", profile_image_url=" +
all_data['user']['profile_image_url'] + " WHERE user_id=" +

I have then printed out the SQL to the screen below:

UPDATE user_table SET following='None', followers_count=252,
favourites_count=3899, friends_count=12, statuses_count=506,
verified=False, profile_image_url='
/735726715267679398/m%HAWxt7_normal.jpg' WHERE user_id=2933205672

I have pasted this SQL into phpmyadmin and ran the SQL and it performed the update without any errors.
Can anyone see a solution to this?

Answer Source

Jens is right about the prepared statements. This is not only hard to debug, it's a security risk too. Your vunerable to SQL Injections.

Anyway, I think your problem here are the missing quotes.

...", profile_image_url='" + all_data['user']['profile_image_url'] + "' WHERE user_id="...
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download