Mount SQL query in Python3 from data in a dictionary

Question

I’m trying to format a query and leave it this way:

INSERT INTO users('username', 'password') VALUES ('meuusuario', 'minhasenha')

But you’re returning me with []:

INSERT INTO users(['username', 'password']) VALUES (['meuusuario', 'minhasenha'])

My function in the memento is like this:

def insert(self, table, data):
    query = "INSERT INTO " + table + "(" + str(list(data.keys())) + ") VALUES (" + str(list(data.values())) + ")"

    return query

The call is like this:

data = {
    'username': 'meuusuario',
    'password': 'minhasenha',
}

print(database.insert('users', data))

Note: it is a class.

Answer 1

In Python there are several ways to manipulate strings and interpolate variables, and concatenate several strings with + is the worst of them.

Your error is that the string representation of a list - str(list(...)) necessarily includes the [ ] - you have to use string manipulation methods to mount the correct representation. But before that, some considerations

Assembling the values of the queries

But, the part of this, when we make queries, if we try to assemble the SQL text by concatenating the strings ourselves, Dams margin to attacks of "SQL Injection" - if the data that are used in SQL has an insecure source (a field in the user interface, for example), strings can be inserted that perform other queries and commands in the database than those desired. The way to avoid this is to "clean up" any and all data that will be used to compose a query. To do this right is not laborious, but to do Right and everywhere is hardly what happens.

The approach of Python then is to make the call itself of the database drivers (the method execute) to run queries in SQL is responsible for including data coming from variables in the query. So, you don’t use any of the ways to concatenate Python strings - rather, a specific SQL driver tag.

There are 3 - and only 3 - different syntaxes for this interpolation - but since you don’t tell which SQL driver you’re using, I can’t get through the exact syntax. But one of them, for example, the one that is used for Sqlite, for positional arguments, is simply to put a ? at the points where the surrogate variables will be used.

In that case, your code would look like this:

conexao.execute("INSERT INTO users('username', 'password') VALUES (?, ?)", (
'meuusuario', 'minhasenha')) ```

Note that the "meuusuario" and "minhasena" are passed in a tuple, no according to parameter for "execute". And that the call execute also inserts the ' necessary - they are not required in query text.

Other SQL drivers use instead of ? the markings {} and %s for the replacement -

riding the darlings themselves

Now, in your example, you want to build dynamic queries using not only variable values, but also column names and even table names.

In this case, formatting method queries execute does not interpolate - it is limited to the sessions of an SQL query where values actually enter. (That is, with the above method you cannot put names of columns or dynamic tables).

In this case, you can use the best form of string interpolation available in Python which are the f-strings - (exist only from Python 3.6).

With "f-strings" you put the prefix "f" before the quotes, and inside the string you use {} to delimit Python expressions that are executed as code - and the string representation of the result of that code is placed in that space.

Avoid SQL Injection continues to equalmetne important, so it is necessary to sanitize all parameters - this should not be neglected, nor believe that another layer of your software, which will be developed at another time does this. Python applications have a record of security of SQL injection failures, precisely because since the beginning of the language has adopted the practice of sanitizing the parameters precisely at the time of mounting the query.

There is no universal way to sanitize the parameters in a generic way that holds for all SQL drivers - but if we do this for the characters \ ; ' we’ve already avoided problems, so it’s best to have a separate function to do this:

def escape(name):
    # O prefixo "r" é pra evitar que a '\' seja consumida pelo próprio parser do Python
    escape_chars = r"\';" 
    for char in escape_chars:
        name = name.replace(char, rf"\{char}")
    return name

...
def insert(self, table, data):
    e = escape  # encurta o nome da função de escape
    columns = [e(colname) for colname in data.keys()]
    insert_seq = ', '.join(['%s'] * len(data))

    query = f"INSERT INTO {e(table)} ({', '.join(columns) }) VALUES ({insert_seq})"
    return tuple(data.values())

And whoever calls this method gets two back parameters - one with the query text, and the other with the second parameter to be passed to execute - the use is like this:

    query, arguments = self.insert(table, data)
    self.conection.execute(query, arguments)

Note that for simplification purposes I used the %s as a placeholder for SQL arguments, which is the one used by Mysqldb - but this varies depending on the SQL driver. The SQL driver module, whether sqlite, psycopg, mysqldb, or any other, has a parameter paramstyle which defines what type of placeholder to use - (and some require that arguments be passed as a dictionary itself) - so this has to be adapted. The complete table (and other specifications of methods and attributes present in all SQL drivers) is here: https://www.python.org/dev/peps/pep-0249/#paramstyle

---

For the purpose of making the answer complete, it is interesting that you understand the use of the method join of the Python strings that is done above - it is the opposite of split: basically it takes an iterable object as parameter, (whose items should be all strings) and "glue" the initial string as the item separator - ie:

", ".join(["abc", "def", "ghi"]) flipped 'abc, def, ghi'. And we can put a "Generator Expression" that processes elements of the iterable within the Join call - what’s done above to escape all the elements.