(Python MySQLdb) when trying to insert UTF-8 into MySQL

Question

I cannot find a solution. Can you help me with this question please?

    dic={'username':u'\uc774\ud55c\ub098','userid':u'david007', 'nation':u'\ub300\ud55c\ubbfc\uad6d'}
    c=MySQLdb.connect(host=ddb['host'],user=ddb['user'],passwd=ddb['passwd'],db=ddb['db'], use_unicode=True, charset="utf8")
    s=c.cursor()
    sql="INSERT INTO "+db+" "+col+" VALUES "+str(tuple(dic.values()))
    s.execute(sql)

    "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 ''\\uc774\\ud55 ... at line 1")

    print sql
    INSERT INTO user_tb (username, userid, nation) VALUES (u'\uc774\ud55c\ub098', u'david007', u'\ub300\ud55c\ubbfc\uad6d')

And the error is:

That is not even valid Python code. Can you include the cursor .execute() call as stated in your code? It looks like you are mixing up your python string literal syntax in there but I want to see this is detail. — Martijn Pieters
– Martijn Pieters, Commented Nov 13, 2012 at 8:07

Pedro Romano · Accepted Answer · 2012-11-13 08:41:46Z

4

You need to use a parametrised query:

sql = "INSERT INTO " + db + " " + col + " VALUES (%s, %s, %s)"
s.execute(sql, dic.values())

When you simply concatenate the tuple to your query, the u prefix of the unicode strings will make those strings invalid SQL. With parameters MySQLdb, will do the right thing with the parameter replacement (i.e. encoding the unicode strings to a byte representation) and generate valid SQL.

Anyway as a general principle you should always use parameters in your queries to prevent SQL injections.

edited Nov 13, 2012 at 8:41

answered Nov 13, 2012 at 8:35

Pedro Romano

11.2k4 gold badges48 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hynekcer Over a year ago

user1161599@ You did put Python escaped codes like u'\uc774\ud55c\ub098' into SQL, but the database does not understant Python type of escaping and u prefix. Note that "Perdo" correctly does not use parsing of strings into SQL string at all because it would be vulnerable to SQL injection attack. He uses parameters.

Collectives™ on Stack Overflow

(Python MySQLdb) when trying to insert UTF-8 into MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related