Invoking Stored Procedures with Python
Hello, World! In this tutorial, you will learn how to create stored procedures in MySQL and then how to invoke these stored procedures using Python. SQL injection is by far one of the most dangerous vulnerabilities that a web application can have and is in fact ranked as the number one vulnerability in the OWASP Top 10 list under the category of Injection. Many of the breaches and data dumps that you often hear about in the news were possible because of a SQL injection flaw that existed in an application. Let’s go ahead and start by setting up our MySQL database.
Note: to follow along with this article, you will need to install Docker because I will be using MySQL’s Docker container for creating the MySQL database.
Setting up a MySQL database with Docker
Run the following commands in your terminal to download the MySQL Docker image and run the container (there are only two commands below):
docker pull mysqldocker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=[your_password] -d mysql:latest
After running the two commands above, run the following command to gain shell access to the MySQL container:
docker exec -it mysql bash
The command above will give you a shell to the container. To connect the MySQL database, run the following command (this will prompt for a password; use the password specified in the
docker run command we ran previously):
mysql -h localhost -u root -p
You should see a screen similar to the one below:
Ok, now that we are in the MySQL shell, we can create our example database and table using the following commands (make sure to copy every statement delimited by a semi-colon as one command):
It should look something like this:
To test that you successfully created the User’s table, run the following SQL statement in your shell:
SELECT * FROM Users;
You should see the following output:
Ok, great! We have successfully created our example database. Let’s now learn how to create stored procedures in MySQL.
Creating Stored Procedures in MySQL
To create stored procedures, MySQL has two built-in keywords,
PROCEDURE that allows us to create procedures. The following example shows the stored procedure created for this tutorial called
get_email_addr. It accepts two arguments,
password, and will return the email corresponding to the username and password provided. The
DELIMITER is changed from a
// so that we can write multiple SQL statements in between the
END keywords without MySQL executing them. Finally, after finishing our stored procedure, we then set the
DELIMITER value back to the semi-colon (the
DELIMITER commands should be executed individually):
After copying and pasting the commands above into your terminal, your screen should look like this:
To test our stored procedure in MySQL, run the following commands:
CALL get_email_addr("james", "password", @email);SELECT @email;
the output should look like this:
More on how to create stored procedures.
Awesome, we now have a working stored procedure in MySQL that we can now execute with Python.
Invoking Stored Procedures with Python
To interact with our MySQL database using Python, we will need to install the
mysql-connector-python package using the following command:
pip install mysql-connector-python
We can connect to our MySQL database using the
mysql.connector.connect function as follows:
Now that we obtained a connection to our database, we need to obtain a cursor to our database object that will allow us to execute SQL queries which can be done by invoking the
cursor method of our database object. After obtaining the cursor to our database, I created a function named after the stored procedure I created,
get_email_addr, and it takes three arguments: the cursor object we created, the name of the stored procedure in our MySQL database, and a variadic argument containing the arguments for the stored procedure. The
get_email_addr function uses the cursor object that was passed as an argument to execute the stored procedure using its
callproc method that takes in the name of the procedure to invoke and its arguments. The
callproc method returns the arguments passed to the stored procedure and the return value of the procedure, in this case, an email address:
Running the script above with the program arguments,
password, we get the email for the user
You can now use the knowledge discussed in this article to implement stored procedures in your next database-dependent application. Note: Stored procedures aren’t 100% immune to SQL injection vulnerabilities (check out this article on stored procedure SQL injection), but they sure do make it harder to exploit the vulnerability.