Python unicode excape underscore and double quotes

Question

I have data which represent usernames from different languages. I have carries out proper unicoding process as follows:

while attempts < 3 and not success:
    query = ur'''select gu_name from globaluser where gu_name = "{uname}"'''.format(uname=unicode(filerow['user_name'],'utf-8', errors='strict'))
    try:
        self.gdbCursor.execute(query.encode('utf-8'))
        gUser = self.gdbCursor.fetchone()

But when it comes to names like this Name1_"GG"_Name1AnotherName I ended up getting following error:

ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'GG" Cooper"\' at line 1')

How do I properly encode these type of characters?

Update:

Based on the answers provided I did the following:

\'GG" Cooper"\' to resolve user name

                    while attempts < 3 and not success:
                        #query = ur'''select gu_name from globaluser where gu_name = "{uname}"'''.format(uname=unicode(filerow['user_name'],'utf-8', errors='strict'))
                        uName = unicode(filerow['user_name'], 'utf-8')
                        query = ur'''select gu_name from globaluser where gu_name = "%s"'''
                        try:
                            #self.gdbCursor.execute(query.encode('utf-8'))
                            self.gdbCursor.execute((query % (uName)).encode('utf-8'))
                            gUser = self.gdbCursor.fetchone()

But I still get the following error:

ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'GG" Cooper"\' at line 1')

Use parameters inputs instead of string and your names will be properly escaped by the database — Vor, Sep 03 '14 at 19:03
@Vor I updated my code with the answer you provided but didn't really work. I also update the question to show how I did. — add-semi-colons, Sep 04 '14 at 17:50
Yes I ended up getting a operational error when I remove double quotes. `OperationalError: (1054, "Unknown column 'Kelovy' in 'where clause'")` — add-semi-colons, Sep 04 '14 at 18:02
actual username is `somename_"GG"_Cooper` it has quotes so need to be escape after I red the link you provided %s should take care of all that... — add-semi-colons, Sep 04 '14 at 18:05

Vor · Answer 1 · 2014-09-03T19:29:24.400

1

You should be using parameters inputs as it suggested in:

http://legacy.python.org/dev/peps/pep-0249/#id15

Here is an example:

sql = "insert into foo values(%s)"
cursor.execute(sql, ('My very %$@*@"""S weird name',))

edited Sep 03 '14 at 19:29

answered Sep 03 '14 at 19:24

Vor

33,215
43
135
193

Python unicode excape underscore and double quotes

1 Answers1