Python: Best Practice and Securest Way to Connect to MySQL and Execute Queries

Python: best practice and securest way to connect to MySQL and execute queries

To avoid injections, use execute with %s in place of each variable, then pass the value via a list or tuple as the second parameter of execute. Here is an example from the documentation:

c=db.cursor()
max_price=5
c.execute("""SELECT spam, eggs, sausage FROM breakfast
          WHERE price < %s""", (max_price,))

Note that this is using a comma, not % (which would be a direct string substitution, not escaped). Don't do this:

c.execute("""SELECT spam, eggs, sausage FROM breakfast
          WHERE price < %s""" % (max_price,))

In addition, you must not use single quotes around the position holder ('%s') if the parameter is a string as the driver provides these.

Preventing sql injections mysqldb python3.6

As Bruno says in an answer to a related question:

To avoid injections, use execute with %s in place of each variable, then pass the value via a list or tuple as the second parameter of execute.

Following this advice, you can create your SQL like this:

sql ="""INSERT INTO projecten.population (city, state, population)
      VALUES (%s, %s, %s)"""

c.executemany(sql, cities)

This is a much safer approach than what you have now.

Use of '.format()' vs. '%s' in cursor.execute() for mysql JSON field, with Python mysql.connector,

First of all: NEVER DIRECTLY INSERT YOUR DATA INTO YOUR QUERY STRING!

Using %s in a MySQL query string is not the same as using it in a python string.
In python, you just format the string and 'hello %s!' % 'world' becomes 'hello world!'. In SQL, the %s signals parameter insertion. This sends your query and data to the server separately. You are also not bound to this syntax. The python DB-API specification specifies more styles for this: DB-API parameter styles (PEP 249). This has several advantages over inserting your data directly into the query string:

Prevents SQL injection

Say you have a query to authenticate users by password. You would do that with the following query (of course you would normally salt and hash the password, but that is not the topic of this question):

SELECT 1 FROM users WHERE username='foo' AND password='bar'

The naive way to construct this query would be:

"SELECT 1 FROM users WHERE username='{}' AND password='{}'".format(username, password)

However, what would happen if someone inputs ' OR 1=1 as password. The formatted query would then become

SELECT 1 FROM users WHERE username='foo' AND password='' OR 1=1

which will allways return 1. When using parameter insertion:

execute('SELECT 1 FROM users WHERE username=%s AND password=%s', username, password)

this will never happen, as the query will be interpreted by the server separately.

Performance

If you run the same query many times with different data, the performance difference between using a formatted query and parameter insertion can be significant. With parameter insertion, the server only has to compile the query once (as it is the same every time) and execute it with different data, but with string formatting, it will have to compile it over and over again.

How to securely connect to MySQL database

Long story short, anything you publish is "potentially" breakable.

Adding an auth server in the middle will solve the core issue. He connects to your server, your server connects to ... whatever.

Sad thing to say, but basically anything that you're sending a user; just assume it will be cracked. You can make it harder to crack blah blah, but at the end of the day user side security (ie just having the db creds in an exe) is essentially useless. You should literally -never- expose credentials in your app that way.

What is the correct way to use .format when passing other variables in the select query for MySQL?

In you case,

• miss a right parenthesis );
• mix up the usage in str.format and format % values

Try this:

>>> a = "c9" # this is for %s
>>> b = "TEMP" # this is for the first {}
>>> c = "1 hour" # this is for the last {}
>>> sql = "SELECT Client, {}, Date_Time from data where Client = \"{}\" and Date_Time > DATE_SUB(NOW(), INTERVAL {})".format(b,a,c)
>>> sql
'SELECT Client, TEMP, Date_Time from data where Client = "c9" and Date_Time > DATE_SUB(NOW(), INTERVAL 1 hour)'
>>>
>>> cur = db.cursor()
>>> cur.execute(sql)  
>>> data = cur.fetchall()   
>>> cur.close()

Why is PyMySQL not vulnerable to SQL injection attacks?

It's a pity that the designers of pymysql chose to use %s as the parameter placeholder. It confuses many developers because that's the same as the %s used in string-formatting functions. But it's not doing the same thing in pymysql.

It's not just doing a simple string substitution. Pymysql will apply escaping to the values before interpolating them into the SQL query. This prevents special characters from changing the syntax of the SQL query.

In fact, you can get into trouble with pymysql too. The following is unsafe:

cur.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

Because it interpolates the variable symbol into the string before passing it as an argument to execute(). The only argument is then a finished SQL string with the variable(s) formatted into it.

Whereas this is safe:

cur.execute("SELECT * FROM stocks WHERE symbol = %s", (symbol,))

Because it passes the list consisting of the symbol variable as a second argument. The code in the execute() function applies escaping to each element in the list, and interpolates the resulting value into the SQL query string. Note the %s is not delimited by single-quotes. The code of execute() takes care of that.

Using a cursor to access a MySQL database using Python?

The cursor object is a required part of the Python DB API. All database library implementations will do it this way.

---

Related Topics