Protecting Against SQL Injection in Python

Protecting against SQL injection in python

From the documentation:

con.execute("insert into person(firstname) values (?)", ("Joe",))

This escapes "Joe", so what you want is

con.execute("insert into person(firstname) values (?)", (firstname_from_client,))

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.

How do PyMySQL prevent user from sql injection attack?

Python drivers do not use real query parameters. In python, the argument (the variable attack in your example) is interpolated into the SQL string before sending the SQL to the database server.

This is not the same as using a query parameter. In a real parameterized query, the SQL string is sent to the database server with the parameter placeholder intact.

But the Python driver does properly escape the argument as it interpolates, which protects against SQL injection.

I can prove it when I turn on the query log:

mysql> SET GLOBAL general_log=ON;

And tail the log while I run the Python script:

$ tail -f /usr/local/var/mysql/bkarwin.log
...
180802  8:50:47    14 Connect   root@localhost on test
           14 Query SET @@session.autocommit = OFF
           14 Query select id from tables where name='jason\' and 1=1'
           14 Quit  

You can see that the query has had the value interpolated into it, and the embedded quote character is preceded by a backslash, which prevents it from becoming an SQL injection vector.

I'm actually testing MySQL's Connector/Python, but pymysql does the same thing.

I disagree with this design decision for the Python connectors to avoid using real query parameters (i.e. real parameters work by sending the SQL query to the database with parameter placeholders, and sending the values for those parameters separately). The risk is that programmers will think that any string interpolation of parameters into the query string will work the same as it does when you let the driver do it.

Example of SQL injection vulnerability:

attack="jason' and '1'='1"
sqls="select id from tables where name='%s'" % attack
cursor.execute(sqls)

The log shows this has resulted in SQL injection:

180802  8:59:30    16 Connect   root@localhost on test
           16 Query SET @@session.autocommit = OFF
           16 Query select id from tables where name='jason' and '1'='1'
           16 Quit  

How to prevent sql injection for a IN clause using python mysql connector?

You should not use string formatting to create your request, leave argument parsing to the lib with the second parameter of cursor.execute.

Aka:

query = """
    SELECT
        id, quotation_number
    FROM quotations
    WHERE
        quotation_number = %s
    LIMIT 1;
"""
result_in_list_of_dict = cursor.execute(query, (quotation_number,))

Now, to use this with a list is pretty simple

quotation_numbers = [1, 2, 3]
query = f"""
    SELECT
        id, quotation_number
    FROM quotations
    WHERE
        quotation_number IN ({', '.join(['%s'] * len(quotation_numbers))})
;
"""
result_in_list_of_dict = cursor.execute(query, quotation_numbers)

You only have to create a placeholder for every item in your list and pass it to cursor.execute

---

To show the problem string formatting.

fake_args = '"hello"; SELECT 123; -- '
query = "SELECT %s" % fake_args

print(query) # => 'SELECT "hello"; SELECT 123; -- '

By passing args to cursor.execute

fake_args = '"hello"; SELECT 123; -- '
query = "SELECT %s"

cursor.execute(query, (fake_args, ))

# In MySQL log
# SELECT '\"hello\"; SELECT 123; -- '

With string formatting, you have so security against SQL injection (that's not the point of it). mysql-connector-python can do it and should do it.

How to avoid SQL injection with SELECT * FROM {table_name} ?

I think you're confusing the definition of SQL injection. SQL injection is an attack on your software where someone causes your SQL query to do something you didn't want it to. String interpolation is not SQL injection. String interpolation can sometimes enable SQL injection, but not always. To see that string interpolation isn't always unsafe, think about which of the following is safest:

1. sql = 'SELECT name FROM user'
2. sql = 'SELECT name FROM ' + 'user'
3. sql = 'SELECT name FROM %s' % ['user']
4. sql = 'SELECT name FROM {}'.format('user')

Each of these lines of code does the exact same thing, so none of them can be more or less safe than the others. In your exact example, there's no danger of SQL injection, because you're just building a hardcoded SQL query string.

On the other hand, if your table value came from a user, then there could be security issues:

• What if they pass the name of a table that exists, but you didn't want them to query?

  table = 'secrets'
  sql = 'SELECT name FROM %s LIMIT 1' % table
  

  results in:

  SELECT name FROM secrets LIMIT 1
  

• What if they pass something that is not actually a table name?

  table = 'product; DROP TABLE user; --'
  sql = 'SELECT name FROM %s LIMIT 1' % table
  

  results in:

  SELECT name FROM product;
  DROP TABLE user;
  -- LIMIT 1
  

You could prevent this by checking if the table name is allowed:

if table.lower() not in ["user", "group", "partner", "product"]:
    raise Something('Bad table name: %r' % table)

Does SQLAlchemy's filer_by function protect against SQL injection?

Here is the output for postgresql connection

# trying to inject
query = db.query(User).filter_by(username="'user'; drop table user; --"")
print(query)

Output

SELECT "user".id AS user_id, "user".created_at AS user_created_at, "user".updated_at AS user_updated_at, "user".username AS user_username, "user".hashed_password AS user_hashed_password, "user".is_active AS user_is_active, "user".is_superuser AS user_is_superuser 
FROM "user" 
WHERE "user".username = %(username_1)s

String value passes as param to query, so you are protected from SQL injection.

How to protect against SQL Injection with pandas read_gbq

Per Google BigQuery docs, you have to use a specified configuration with SQL parameterized statement:

import pandas as pd

sql = "SELECT name, date FROM table WHERE id = @id"

query_config = {
    'query': {
        'parameterMode': 'NAMED',
        'queryParameters': [
            {
                'name': 'id',
                'parameterType': {'type': 'STRING'},
                'parameterValue': {'value': 1}
            }
        ]
    }
}

df = pd.read_gbq(sql, project_id='project-1622', location='EU', configuration=query_config)

Will this code prevent SQL injection (Python)

The concept of the %s is to isolate the data from the query. When you pass two arguments, they are combined in the module. It is intended to mitigate injection, but I'd be hesitant to say "100%"

Edit: many wiser than myself (maybe even real life security experts!) have weighed in here:
https://security.stackexchange.com/questions/15214/are-prepared-statements-100-safe-against-sql-injection

Is SQL injection protection built into SQLAlchemy's ORM or Core?

in your example above i dont see any variable beeing supplied to the database query. Since there is no user supplied input there is also no Sql Injection possible.

Even if there would be a user supplied value as long as you dont use handwritten sql statements with sqlalchemy and instead use the orm model approach (model.Sensor.__table__.select()) as can be seen in your example you are secure against Sql Injection.

In the end its all about telling sqlalchemy explicitely what columns and tables should be used to select and insert data from/to and keeping that separate from the data that is beeing inserted or selected. Never combine the data string with the query string and always use sqlalchemy orm model objects to describe your query.

Bad way (Sql Injectable):

Session.execute("select * form users where name = %s" % request.GET['name'])

Good way (Not Sql Injectable):

Session.execute(model.users.__table__.select().where(model.users.name == request.GET['name']))

---

Related Topics