Python pymySQL sending a string with quotes

Question

I'm trying to insert a string to a mySQL database like this:

cur.execute('UPDATE connections SET cmd = "' + command + '", client_new = 1 where ip = "' + ip + '"')

When I set command to be echo "hello", I get this error:

Traceback (most recent call last):
  File "C:/Users/Owner/Desktop/master.py", line 111, in <module>
    main ()
  File "C:/Users/Owner/Desktop/master.py", line 80, in main
    print(""); command(); print("")
  File "C:/Users/Owner/Desktop/master.py", line 29, in command
    cur.execute('UPDATE connections SET cmd = "' + command + '", client_new = 1 where ip = "' + ip + '"')
  File "M:\Python34\lib\site-packages\pymysql\cursors.py", line 146, in execute
    result = self._query(query)
  File "M:\Python34\lib\site-packages\pymysql\cursors.py", line 296, in _query
    conn.query(q)
  File "M:\Python34\lib\site-packages\pymysql\connections.py", line 781, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "M:\Python34\lib\site-packages\pymysql\connections.py", line 942, in _read_query_result
    result.read()
  File "M:\Python34\lib\site-packages\pymysql\connections.py", line 1138, in read
    first_packet = self.connection._read_packet()
  File "M:\Python34\lib\site-packages\pymysql\connections.py", line 906, in _read_packet
    packet.check_error()
  File "M:\Python34\lib\site-packages\pymysql\connections.py", line 367, in check_error
    err.raise_mysql_exception(self._data)
  File "M:\Python34\lib\site-packages\pymysql\err.py", line 120, in raise_mysql_exception
    _check_mysql_exception(errinfo)
  File "M:\Python34\lib\site-packages\pymysql\err.py", line 112, in _check_mysql_exception
    raise errorclass(errno, errorvalue)
pymysql.err.ProgrammingError: (1064, '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 \'hello"", client_new = 1 where ip = "123.456.789.012"\' at line 1')

I understand that there is a problem with how I'm passing the data to the database, but how do I do it properly?

It appears you are having problems with quotation marks. I'd try to make a variable nawet 'query', then build query string in it and lastly print it to see if it's proper mySQL statement. — grael, Oct 04 '15 at 11:41

Eugene Soldatov · Accepted Answer · 2018-05-07T13:07:45.987

4

You could use placeholders, in that case mysql driver escapes your query:

cur.execute('UPDATE connections SET cmd=%s, client_new=1 where ip=%s', (command, ip))

edited May 07 '18 at 13:07

answered Oct 04 '15 at 11:43

Eugene Soldatov

9,755
2
35
43

score 0 · Answer 2 · answered Oct 04 '15 at 11:45

0

You are surrounding your SQL string literals with double quotes. SQL requires string literals to have single quotes.

In SQL, double quotes normally signify identifier names, e.g. if you had a column name with a space in it, you would use double quotes then.

answered Oct 04 '15 at 11:45

Miner_Glitch

537
4
16

http://stackoverflow.com/questions/1992314/what-is-the-difference-between-single-and-double-quotes-in-sql – Eugene Soldatov Oct 04 '15 at 11:53
MySQL happily accepts double quotes for both string and other (i.e. integer) values. e.g. insert into rr set idA=1,idB="1",name= Cigar"; works just fine – simpleuser May 05 '18 at 15:50
AWS RDS still does not accept double quotes unfortunately. – Flair Nov 05 '20 at 20:06

Python pymySQL sending a string with quotes

2 Answers2

Linked