0

Python mysql default escape function, corrupts the query. Original Query string is following. It works fine and does add records to database as desired

INSERT IGNORE INTO state (`name`, `search_query`, `business_status`, `business_type`, `name_type`, `link`) VALUES ("test_name1", "test", "test_status", "test_b_typ", "test_n_typ", "test_link"), ("test_name2", "test", "test_status", "test_b_typ", "test_n_typ", "test_link")

But After escaping it to make sql Injection secure using the fuction safe_sql = self.conn.escape_string(original_sql) safe_sql being generated is following

b'INSERT IGNORE INTO state (`name`, `search_query`, `business_status`, `business_type`, `name_type`, `link`) VALUES (\\"test_name1\\", \\"test\\", \\"test_status\\", \\"test_b_typ\\", \\"test_n_typ\\", \\"test_link\\"), (\\"test_name2\\", \\"test\\", \\"test_status\\", \\"test_b_typ\\", \\"test_n_typ\\", \\"test_link\\")'

Now if I try to execute the safe_sql I get the syntax error below

MySQLdb._exceptions.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 \'\\"test_name1\\", \\"test\\", \\"test_status\\", \\"test_b_typ\\", \\"test_n_typ\\", \\"tes\' at line 1')

Which makes me wonder that if escape function I am using is either broken / uncompatibl or I am not using it the right way ? Also i am entering hundreds of records at one time, and due to the fast processing (which i purely assume) of single query as compared to prepared statements running hundreds of time, I am creating a large query.

  • Try to set values in single quote ( 'your_value_1', 'value_2' ) – Wonka Sep 26 '19 at 08:15
  • 1
    Explain to me WHY you are not using a prepared statement? Just replace all the values with `?` (or `%s`, depending on the which driver you are using) and pass a `tuple` with the values to the `execute` statement. Check out this example https://pymysql.readthedocs.io/en/latest/user/examples.html – Klemen Tusar Sep 26 '19 at 08:17
  • I did explain ! because entering hundreds of records by looping over a prepared statement is slower than a single query. –  Oct 03 '19 at 07:36
  • Though as @deceze explained, executemany is the way to go in this case –  Oct 03 '19 at 07:37

1 Answers1

0

You can't escape the entire query! You can't construct a query by randomly concatenating strings and then wave a magic wand over it and make it "injection secure". You need to escape every individual value before you put it into the query. E.g.:

"INSERT ... VALUES ('%s', ...)" % self.conn.escape_string(foo)

But really, your MySQL API probably offers prepared statements, which are much easier to use and less error prone. Something like:

self.conn.execute('INSERT ... VALUES (%s, %s, %s, ...)',
                  (foo, bar, baz))
deceze
  • 510,633
  • 85
  • 743
  • 889
  • As i mentioned, if I am adding say 1000 records: Wont executing single query a much faster solution than executing a prepared statement 1000 times. Is there a work around. or can we like provide a prepared statement to run providing it 1000 tuples at one ? –  Sep 26 '19 at 08:25
  • 1
    1) You could still create one query which inserts several records using prepared statements. 2) Your API probably has an [`executemany`](https://pymysql.readthedocs.io/en/latest/modules/cursors.html#pymysql.cursors.Cursor.executemany) method, which can take care of this as well. – deceze Sep 26 '19 at 08:28
  • 1
    FYI, you may want to read https://stackoverflow.com/a/6327754/476 and https://stackoverflow.com/a/12720360/476. – deceze Sep 26 '19 at 08:30
  • Yeah execute many is the way to go, as in docs, the sole purpose of it is to make it faster than executing single prepared statement in a loop. Thats perfect solution ! –  Sep 26 '19 at 08:32
  • 1
    Comments are not for extended discussion; this conversation has been [moved to chat](https://chat.stackoverflow.com/rooms/200000/discussion-on-answer-by-deceze-python-mysql-escape-function-generates-corrupted). – deceze Sep 26 '19 at 08:53