Preventing SQL injections in Python (and other vulnerabilities)

Preventing SQL injections in Python (and other vulnerabilities)

Python is a wonderful language, ideal for beginners, and easy to scale up from starter projects to complex applications for data processing and serving dynamic web pages. But as you increase complexity in your applications, it can be easy to inadvertently introduce potential problems and vulnerabilities. In this article, I will highlight the easiest to miss that can cause the biggest problems, how to avoid them and tools and services that help you save time doing so.

User input

There are differences between how Python 2 and 3 handle user input from a user, and it’s easy to use the incorrect function for a Python version and not get the results you expect. Take for example:

person = input('Enter text: ')
print('You wrote ' + person)

In Python 3, this results in what you expect, but Python 2 evaluates the input as a variable name, which is likely not what you want, or worse, if someone enters another Python method, this can open your application up to a world of potential vulnerabilities.

A simple fix with Python 2 code is to instead use raw_input which behaves the same way as input in Python 3.

The example above sets the scene but is not a dangerous example. Behind the scenes, Python 2 is evaluating the input and executing it with eval(). This allows for flexible code, but is potentially dangerous. In the example below, a user is able to import a Python module and execute a command to delete a file.

eval("import('os').system('rm sample.txt')")

Again, using raw_input with Python 2, or input with Python 3 will analyze the input and sanitize it.

Using eval() directly is also riddled with danger for the reasons outlined above. Fortunately, it has optional arguments to restrict what eval() is allowed to execute.

eval(expression[, globals[, locals]])

Setting the second argument to {"__builtins__":} will deny eval() access to any builtin Python methods and you can use the third argument to set the local functions and variables eval() is allowed access to. This is an approach to making eval() more secure, but it requires a lot of deny-listing work, and something can always slip through the net. For further reading on this (complex) issue, I recommend this post from Ned Batchelder

Database inputs

Databases are a common way to store and access dynamic datasets and have been a fundamental part of application development for decades. However, they introduce the ability for users to input dangerous content to your application and database. They are called SQL injections.

The example here uses a MySQL database, but similar principles apply if you are using Postgres (with the psycopg package), or SQLlite (with the sqllite package).

Before we get to how to SQL injections works, let’s set up MySQL database and see how to connect to it using python. In this post, I will create the setup on Kali Linux. You can create a similar setup on Windows and macOS as well.

Setting up MySQL for Python

First, you will need to install MySQL server. Kali Linux comes with MySQL installed. But if for some reason it isn’t, you can use the following command on the terminal to install it:

apt-get install mysql-server

Next step is to start the MySQL server. You can do it by running:

service mysql start

And you can verify if the MySQL service was started by running:

Let’s create a separate MySQL user for our little python experiment. You can use the following commands:

mysql -u root 
create user 'newuser'@'localhost' identified by 'pass'; 
grant all privileges on . to 'newuser'@'localhost'; 
flush privileges;

Next, let’s create a table and add some data to the table.

create database sample; 
show databases; 
use sample; 
create table userdata(Name varchar(10), Country varchar(3)); insert into userdata (Name, Country) values ('Tony', 'US'), ('Steve','UK'), ('Mary', 'IN'); select * from userdata;

Now you have python and MySQL ready. But to use MySQL using Python, you need a MySQL connector. You can install the connector using pip by running the following command:

Continuing the example above to allow a user to enter a value they are looking for in a database table:

import mysql.connector 
db = mysql.connector.connect(host="localhost", user="newuser", passwd="pass", db="sample") 
cur = db.cursor() 
name = raw_input('Enter Name: ') 
cur.execute("SELECT * FROM userdata WHERE Name = '%s';" % name) for row in cur.fetchall(): print(row) 
db.close()

SQL Injection in Python

If the user enters a legitimate search value, for example, Tony, then all is well. But if they try something untoward, for example, Tony'; DROP TABLE userdata;, then the results are fatal.

Thankfully, the solution is simple, change the SQL statement to the following:

cur.execute("SELECT * FROM userdata WHERE Name = %s;", (name,))

This small syntax sanitizes the input for you, resulting in a much better outcome.

You can verify that the database table wasn’t deleted from within the database as well.

This is just one example of how SQL injections can be harmful. You can find more examples of SQL injections and their effects here.

Insecure packages

When you import a module into your Python application, the interpreter will run the code. This means you need to be cautious when importing modules, PyPi is a wonderful resource, but the submitted code is not checked, and malicious packages have found their way into PyPi named with common misspellings. How many times have you added a package to your system without checking its content or origins.

If you’re unsure as to the authenticity and content of an external package, do some research and leave it well alone if you’re still uncertain.

More security issues

This short list was far from comprehensive, here’s a handful of other potential issues to watch for:

• SQL, HTML and JavaScript snippets embedded in source code or templates.
• API keys included in source code.
• References to internal hostnames or staging environments.
• File system access.
• HTTP calls to internal or external web services.

Identifying vulnerabilities

The first steps to preventing most problems with code are to create a checklist of potential issues and check for their hopeful absence. This can be a part of your testing regime or a step before testing. Ideally, you should ask someone else to check your code, as spending hours staring at the same lines can cause ‘code-blindness’ and you may not notice small details anymore. Naturally, this is not a new process, and there are numerous tools to help you such as Crucible, Upsource or both GitHub and GitLab are adding features to help with the process.

Ideally, you should test with as many methods as possible, as in my opinion checking application code too much is not possible. If you are looking for guidance on whether you should trust a tool or not, then read forums, or the OWASP (Open Web Application Security Project) site, which is hard to navigate, but a great source of information.

Use linters and static analysis tools

Linters provide guidance on code best practices and are part of the domain of static application security testing (SAST) tools that analyze your code. You can use them manually as part of your editor or local development process, or as part of an automated testing process. Python has several linters available, including:

• Pylint: The de facto linter for Python, it doesn’t focus on security, but will highlight poor code practices that may lead to vulnerabilities.
• Bandit: A linter from the OpenStack security group that focuses specifically on security issues.

Python IDEs such as PyCharm and Wingware typically have these tools (and others) built in, and there are often plugins for text editors available.

Use dynamic application security testing

The other major tool domain is dynamic application security testing (DAST) that involves operational testing, i.e. how the application actually works and how people might try to ‘break’ it. You can use these tools locally as you code, or integrate them with your testing and deployment strategy.

An example of creating your own tool to match your requirements is DASTProxy from eBay. It checks Python code submitted against common potential vulnerabilities and raises JIRA tickets for anything found.

One of the biggest complains with SAST and DAST are of course the flood of reports with false positives. Cisco’s 2017 Annual Cybersecurity Report found that only half of the security alerts organizations receive are deemed legitimate.

Use application security monitoring and protection tools

A more modern security approach integrated with DevOps and software development best practices is to have real-time protection for web applications.

Sqreen takes this a step further, adding a small library to your applications that perform real-time security monitoring as users interact with your applications. It will notify you of any vulnerabilities as they happen and block attacks without false positives, giving you time to fix them.

Stay safe

As applications become increasingly complex and ‘parties’ more determined to break them, security has never been so important. Identifying potential issues before code is in production is an essential step for modern developers, but with new vulnerabilities identified in tech stacks on a weekly basis, you also need to keep constantly up to date. The tools I have mentioned in this article will help you both, and I would love to hear your experiences on the front line, identifying and preventing unrequited attention to your applications.

How to protect your app without pain?

At Sqreen, we believe developers should be able to focus on developing their applications without having to constantly fear for security. We also believe developers deserve security tools that look like the other tools they use every day.

Sqreen will block attacks in your application (including NoSQL injections, SQL injections or XSS) without you having to take any action or to change your code. The best thing is that Sqreen takes literally 30 seconds to install in any app.

About the Author

I explain cool tech to the World. I am a Technical Writer and blogger. I have crazy projects in progress and will speak to anyone who listens. Follow me on Twitter or checkout my website.