Wrong sql syntax because of single or double quote in string

Question

sql = "INSERT IGNORE INTO users_time_zone (user_id, email, username, gcm_reg_id, time_zone) " \
                  "VALUES(%s, '%s', '%s', '%s', '%s') ON DUPLICATE KEY UPDATE gcm_reg_id ='%s', id=LAST_INSERT_ID(id) " % \
                  (user_data['user_id'], user_data['email'], user_data['username'],
                   user_data['gcm_reg_id'], user_data['time_zone'], user_data['gcm_reg_id'])
            print sql
            cursor = conn.cursor()
            cursor.execute(sql)
        conn.commit()

some times username is like dinda_a'yunin or dinda_a"yunin which makes sql like this

INSERT IGNORE INTO users_time_zone (user_id, email, username, gcm_reg_id, time_zone) VALUES(4298849, 'dinda_a'yunin@yahoo.com', 'dinda_ayunin', 'APA91bGRvg74', 'Asia/Jakarta') ON DUPLICATE KEY UPDATE gcm_reg_id ='APA91bGRvg74', id=LAST_INSERT_ID(id)

That makes wrong syntax is there anyway to solve that

score 1 · Accepted Answer · edited May 23 '17 at 12:14

1

Your code is vulnerable towards SQL Injection here...

You either want to use prepared statement:

sql = "INSERT IGNORE INTO users_time_zone (user_id, email, username, gcm_reg_id, time_zone) " \
      "VALUES(%s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE gcm_reg_id =%s, id=LAST_INSERT_ID(id) " 

data = (user_data['user_id'], user_data['email'], user_data['username'],
        user_data['gcm_reg_id'], user_data['time_zone'], user_data['gcm_reg_id'])

cursor = conn.cursor()
cursor.execute(sql, data)

Or escape data manually:

data = list(conn.escape_string, data)

sql = "INSERT IGNORE INTO users_time_zone (user_id, email, username, gcm_reg_id, time_zone) " \
      "VALUES(%s, '%s', '%s', '%s', '%s') ON DUPLICATE KEY UPDATE gcm_reg_id ='%s', id=LAST_INSERT_ID(id) "\
        % data

edited May 23 '17 at 12:14

Community

1
1

answered May 05 '15 at 07:56

Vyktor

20,559
6
64
96

I am still getting same error _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 'dinda_a\\'yunin@yahoo.com'', ''dinda_ayunin'', ''APA91bGRvg74L3Z5mNzGYq8TwQiur99T' at line 1") – jayprakashstar May 05 '15 at 09:58
@JayPrakash I forgot that `escape_string` adds also quotes around the string, updated my answer to reflect that (just remove `'`' from around `%s`) and it should work. – Vyktor May 05 '15 at 10:02

Wrong sql syntax because of single or double quote in string

1 Answers1

Linked