0

I know that this question has been asked in the past, but thorough searching hasn't seemed to fix my issue. I'm probably just missing something simple, as I'm new to the Python-mysql connector supplied by mysql.

I have a Python script which accesses a mysql database, but I'm having issues with removing quotes from my query. Here is my code:

import mysql.connector

try:
    db = mysql.connector.connect(user='root', password='somePassword', host='127.0.0.1', database='dbName')
    cursor = db.cursor()
    query = "select * from tags where %s = %s"
    a = 'tag_id'
    b = '0'
    cursor.execute(query, (a, b))
    print cursor
    data = cursor.fetchall()
    print data
except mysql.connector.Error as err:
    print "Exception tripped..."
    print "--------------------------------------"
    print err

cursor.close()
db.close()

My database is set up properly (as I'll prove shortly).

My output for this program is:

MySQLCursor: select * from tags where 'tag_id' = '0'
[]

Yet when I change my query to not use variables, for example:

cursor.execute("select * from tags where tag_id = 0")

Then my output becomes:

MySQLCursor: select * from tags where tag_id = 0
[(0, u'192.168.1.110')]

To me, this means that the only difference between my Cursor queries are the quotes.

How do I remove them from the query?

Thanks in advance.

Jesse485
  • 13
  • 1
  • 3
  • 1
    what exactly is this about? – e4c5 Jul 20 '16 at 06:19
  • 1
    You can only use parameters for the `where` condition, ie `... WHERE tag_id = %s`. – DeepSpace Jul 20 '16 at 06:20
  • if you want to remove the quotes just do ```cursor.execute(query%(a, b))``` – dermen Jul 20 '16 at 06:22
  • 1
    @dermen That is not a good idea, as it uses Python's string formatting instead of the parameterized query engine which is a very bad practice. – DeepSpace Jul 20 '16 at 06:25
  • Have you tried `b = 0` instead of `b = '0'`? – user94559 Jul 20 '16 at 06:34
  • oh, oops, I just read [this](http://stackoverflow.com/a/775399/2077270), so dont do what I suggested (if this is part of any serious application...) Maybe it is complaining (as smarx suggested) because you are passing a string to an (presumably) integer column. If you let ```b = 0```, dont forget to re-define query as ```query = "select * from tags where %s = %d"``` – dermen Jul 20 '16 at 06:41
  • I'm not sure my suggestion will work... you'll end up with `where 'tag_id' = 0`, but I think for a quoted string to work as a column name in MySQL, it needs to be in either backticks or double quotes. From my reading of the mysql-connector code, I'm not sure it's possible to get this library to emit that. I certainly can't recommend doing your own escaping. :-( – user94559 Jul 20 '16 at 06:45
  • 1
    @dermen I don't think `%d` is valid in this library. It should remain `%s`. – user94559 Jul 20 '16 at 06:46

2 Answers2

2

I personally believe this code is correct and safe, but you should be extremely skeptical of using code like this without carefully reviewing it yourself or (better yet) with the help of a security expert. I am not qualified to be such an expert.

Two important things I changed:

  1. I changed b = '0' to b = 0 so it ends up as a number rather than a quoted string. (This part was an easy fix.)
  2. I skipped the built-in parameterization for the column name and replaced it with my own slight modification to the escaping/quoting built in to mysql-connector. This is the scary part that should give you pause.

Full code below, but again, be careful with this if the column name is user input!

import mysql.connector

def escape_column_name(name):
    # This is meant to mostly do the same thing as the _process_params method
    # of mysql.connector.MySQLCursor, but instead of the final quoting step,
    # we escape any previously existing backticks and quote with backticks.
    converter = mysql.connector.conversion.MySQLConverter()
    return "`" + converter.escape(converter.to_mysql(name)).replace('`', '``') + "`"

try:
    db = mysql.connector.connect(user='root', password='somePassword', host='127.0.0.1', database='dbName')
    cursor = db.cursor()
    a = 'tag_id'
    b = 0
    cursor.execute(
        'select * from tags where {} = %s'.format(escape_column_name(a)),
        (b,)
    )
    print cursor
    data = cursor.fetchall()
    print data
except mysql.connector.Error as err:
    print "Exception tripped..."
    print "--------------------------------------"
    print err

cursor.close()
db.close()
user94559
  • 59,196
  • 6
  • 103
  • 103
  • Thank you, this worked! My final code won't be using user inputs for the column names, but for testing it will come in very handy. Thanks for the response and for the sql injection warnings, much appreciated! – Jesse485 Jul 20 '16 at 17:57
0

I encountered a similar problem using pymysql and have shown my working code here, hope this will help.

What I did is overwrite the escape method in class 'pymysql.connections.Connection', which obviously adds "'" arround your string.

better have shown my code:

from pymysql.connections import Connection, converters


class MyConnect(Connection):
    def escape(self, obj, mapping=None):
        """Escape whatever value you pass to it.

        Non-standard, for internal use; do not use this in your applications.
        """
        if isinstance(obj, str):
            return self.escape_string(obj)  # by default, it is :return "'" + self.escape_string(obj) + "'"
        if isinstance(obj, (bytes, bytearray)):
            ret = self._quote_bytes(obj)
            if self._binary_prefix:
                ret = "_binary" + ret
            return ret
        return converters.escape_item(obj, self.charset, mapping=mapping)


config = {'host':'', 'user':'', ...}
conn = MyConnect(**config)
cur = conn.cursor()
Sun Lemuria
  • 61
  • 4