How to escape from a string with single quote inside to do an insert?

Question

I'm trying to insert N"Pepito Malasanya O'Dhogerty" on Python 3 but cannot escape the single quote, the error on PYODBC is:

"Incorrect syntaxis".

I know that I have to add another one (O''Dhogerty) but I did my best trying everything I saw and didn't fix it.

That's what I did so far:

string = string.replace("'", "\\'\\'")

UPDATED:

It didn't work because I was using 'N' front of the string (because I have characters like "ª")

Now I have:

row[0] = row[0].replace("'","\'\'")

pyodbc.ProgrammingError: ('42S22', "[42S22] [Microsoft][ODBC SQL Server Driver][SQL Server]El nombre de columna 'Pepito Malasanya O''Dhogerty' no es válido. (207) (SQ LExecDirectW)")

What am I doing wrong? I'm going to check parameterized query as you said.

Does this answer your question? [How do I escape a single quote in SQL Server?](https://stackoverflow.com/questions/1586560/how-do-i-escape-a-single-quote-in-sql-server) — Dale K, May 21 '20 at 09:29
Use a parameterized query instead. This will avoid the escape ugliness and provides [many other benefits](https://www.dbdelta.com/why-parameters-are-a-best-practice/). — Dan Guzman, May 21 '20 at 09:36
What is the purpose of the backslash? Should it just be `replace("'","''")` — Dale K, May 21 '20 at 09:58

score 0 · Accepted Answer · answered May 22 '20 at 10:43

SOLUTION:

I was doing the execute method with '{}'.format... but it doesnt work with inserts with such special characters, i changed to cursor.execute("INSERT INTO table VALUES (?,?,?,?,?,?,?,?,?,?)",value1, value2....) and ANY PROBLEM

Thanks anyway for the help

How to escape from a string with single quote inside to do an insert?

1 Answers1