0

There is "a'GG'g-CH2OHCH2CH2OH;v=0;" string in a row of my database. This string include " signs. I want to change " character with ' character. but I 'am getting this error message: OperationalError: near "GG": syntax error. My query is like that:

query_string = "UPDATE " + NameOfPartFuncTable + " SET " + ColumnNameForNamePartFunc + " = " + "\'a\'GG\'g-CH2OHCH2CH2OH;v=0;\'"  + " WHERE (" + ColumnNameForNamePartFunc + " = " + "\"a\'GG\'g-CH2OHCH2CH2OH;v=0;\"" + " )"

I think this problem related to escape character problem. How can i solve this problem?

Patrick Artner
  • 50,409
  • 9
  • 43
  • 69
yasminpoyraz
  • 11
  • 2

2 Answers2

0

You're not capturing the quotes within the text properly.

For example, the WHERE clause becomes:

WHERE (column= "a\'GG\'g-CH2OHCH2CH2OH;v=0;")

The outer quotes are needed for all text values if you're building a query string like this, but they're not quotes within the value itself.

You're better off using the REPLACE function if all you want to do is replace " with ', and you can do this in a parameterised query like this:

c = conn.cursor()
c.execute("UPDATE table SET column = replace( column, ?, ? ) WHERE column = ?;", ('"', "'", '''"a'GG'g-CH2OHCH2CH2OH;v=0;"'''))
c.commit()

where table and column are your table and column names respectively. Notice the triple quotes around the value. A parameterised query is safer to use than formatted strings as they protect against SQL injection attacks.

jignatius
  • 6,304
  • 2
  • 15
  • 30
  • Thank you for your anwer but i do not understand the meaning of ? – yasminpoyraz May 25 '20 at 10:59
  • @yasminpoyraz `?` is a placeholder. It gets replaced by the actual values you supply in the second part of `execute`. The advantage is better security against SQL injection attacks and automatic type conversion. It's the [recommended practice](https://docs.python.org/3.9/library/sqlite3.html). I hope my answer worked for you? – jignatius May 25 '20 at 11:49
  • When I run following query = "SELECT (" + ColumnNameForNamePartFunc + " ) FROM " + NameOfPartFuncTable cursor.execute(query) row = cursor.fetchall() print("row[5] = ", row[5]) row[5] = ("a'GG'g-CH2OHCH2CH2OH;v=0;",) Before, I thougt, this column should be string but when I run that query= "SELECT typeof(" + ColumnNameForNamePartFunc + " ) FROM " + NameOfPartFuncTable I've got ‘text’ answer. I'm sory because I am new in sqlite and python. And unfortunatally I can't change double quoto signs to single quoto in this text. Can this stiation change anything in this query? – yasminpoyraz May 28 '20 at 00:27
  • @yasminpoyraz The column is a text/string type. `cursor.fetchall()` returns a list of tuples. Updating the results will not update the value in the database. You will have to do an update query for that. The main issue you have is how form a valid Python string. You can do that with triple outer quotes or escaping with \: `'''"a'GG'g-CH2OHCH2CH2OH;v=0;"'''` or `"\"a'GG'g-CH2OHCH2CH2OH;v=0;\""`. Then pass this into a parameterised query as I've said in my answer. – jignatius May 28 '20 at 05:37
0

From the SQLite syntax documentation, string literals should be enclosed with single quotes. When you want to include a single quote in the string, it is done by inputing two consecutive single quotes.

So the SQL statement you want to execute would be:

UPDATE tablename SET columnname = '''a''GG''g-CH2OHCH2CH2OH;v=0;''' WHERE columnname = '"a''GG''g-CH2OHCH2CH2OH;v=0;"'

Then you have the issue of how to construct this string in python. In python, when enclosing a string literal with single-quotes, there is no need to escape double-quotes within that string, and likewise when enclosing with double-qoute. For different types of string literals and how escaping works in python, see this.

Based on that, this is one way to do it (assuming you are on python 3.5 or newer)

old_str = "\"a'GG'g-CH2OHCH2CH2OH;v=0;\"".replace("'", "''")
new_str = "'a'GG'g-CH2OHCH2CH2OH;v=0;'".replace("'", "''")
query_string = "UPDATE {tbl} SET {col} = '{new_str}' WHERE ( {col} = '{old_str}' )".format(
    tbl = NameOfPartFuncTable,
    col = ColumnNameForNamePartFunc,
    old_str = old_str,
    new_str = new_str
)
faidros
  • 56
  • 4
  • Thank you but i can not. When I run this query basically query_string = "SELECT (" + ColumnNameForNamePartFunc + " ) FROM " + NameOfPartFuncTable and when i print the 778. row i got this result: ("a'GG'g-CH2OHCH2CH2OH;v=0;",), I only want to change double-quotes with single-quotes. – yasminpoyraz May 25 '20 at 11:03