1

I want to insert a \ before each quotes in my string to insert it to my SQL database using pymysql. If I don't escape quotes I can not insert strings in my database.

For exemple:

str = "ok I'm ready"

must be :

str = "ok I\'m ready"

but print str must be : "ok I'm ready"

To perform it I have done:

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

But it's not working and I still can not insert my string in my database. I have the error message :

(1064, u"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 's Semantic Search software can power everything from intuitive chatbots to searc' at line 1"): ProgrammingError Traceback (most recent call last): File "/var/task/setter_organizations.py", line 38, in handler structured_data.insert() File "/var/task/setter_organizations.py", line 107, in insert self.rds.insertItem(self.type, self.data) File "/var/task/RDS/rds.py", line 92, in insertItem return self.insert(req) File "/var/task/RDS/rds.py", line 35, in insert affected_rows = self.cursor.execute(request) File "/var/task/RDS/pymysql/cursors.py", line 166, in execute result = self._query(query) File "/var/task/RDS/pymysql/cursors.py", line 322, in _query conn.query(q) File "/var/task/RDS/pymysql/connections.py", line 856, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered) File "/var/task/RDS/pymysql/connections.py", line 1057, in _read_query_result result.read() File "/var/task/RDS/pymysql/connections.py", line 1340, in read first_packet = self.connection._read_packet() File "/var/task/RDS/pymysql/connections.py", line 1014, in _read_packet packet.check_error() File "/var/task/RDS/pymysql/connections.py", line 393, in check_error err.raise_mysql_exception(self._data) File "/var/task/RDS/pymysql/err.py", line 107, in raise_mysql_exception raise errorclass(errno, errval) ProgrammingError: (1064, u"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 's Semantic Search software can power everything from intuitive chatbots to searc' at line 1")

I also would print my string and don't see the \

Does anyone know how can I do ?

Jaky Chane
  • 85
  • 1
  • 2
  • 10
  • "it's not working" is not a precise enough error description for us to help you. *What* isn't working? *How* isn't it working? What trouble do you have with your code? Do you get an error message? What is the error message? Is the result you are getting not the result you are expecting? What result do you expect and why, what is the result you are getting and how do the two differ? Is the behavior you are observing not the desired behavior? What is the desired behavior and why, what is the observed behavior, and in what way do they differ? – Jörg W Mittag Jun 29 '17 at 09:02
  • this is because \ is an escape character. Add another one and you're fine – Flo Jun 29 '17 at 09:03
  • So basically you want to escape such characters when your inserting it into the SQL database using an SQL query correct? – hridayns Jun 29 '17 at 09:12
  • yes exactly @code_byter – Jaky Chane Jun 29 '17 at 09:14
  • @JakyChane Please see my edit. – hridayns Jun 29 '17 at 09:17
  • @JakyChane I have edited again, for MySQL specific answer. Please post your MySQL connection code as well, if possible. – hridayns Jun 29 '17 at 09:21
  • @JakyChane it sounds like you are trying to find a way to escape the `'` characters in a string without using a `\ ` - this is oxymoronic. An escaped mysql string contains `\ `, there's no way around that afaik. – Stael Jun 29 '17 at 09:28

5 Answers5

6

You should add a second \:

your_str= your_str.replace("'", "\\'")
Carles Mitjans
  • 4,786
  • 3
  • 19
  • 38
5

This works, but is not what the OP wanted:

str = "ok I'm ready"
str = str.replace("'", "\\'")
print(str)

EDIT:

Please take a look at this question: A good way to escape quotes in a database query string?

And this other one: Escape string Python for MySQL They used:

conn.escape_string()

EDIT 2: Please see this: Python pymySQL sending a string with quotes

and note how they have used placeholders:

cur.execute('UPDATE connections SET cmd=%s, client_new=1 where ip=%s', (command, ip))
hridayns
  • 697
  • 8
  • 16
2

No problem simply use r before text like following: 

str = r"ok I\'m ready"

No need to replace.

1

if you try this will displayed "ok I\\'m ready" in terminal actually its "ok I\'m ready" you need to print it .

The result '\&' is only displayed - actually the string is \&:

str = "ok I'm ready"
str = str.replace("'", "\\'")

or 

str = "ok I'm ready".replace("'","\\'")

do, 

print str  # python 2

print(str) # python 3
Mohideen bin Mohammed
  • 18,813
  • 10
  • 112
  • 118
0

You can use double backlash:

str = "ok I\\'m ready"

Edit: Now that you clarify, a way to solve it is json.dumps("I'm")

Snow
  • 1,058
  • 2
  • 19
  • 47