Steps for the prevention of SQL Injection attacks in an application created with Python and MySQL.
First, create a virtual environment:
To install the necessary packages:
Create an .env file (in the root of the project) for the environment variables:
. from decouple import config
import pymysql
import mysql.connector
def get_connection():
return mysql.connector.connect(
host=config('MYSQL_HOST'),
database=config('MYSQL_DB'),
user=config('MYSQL_USER'),
password=config('MYSQL_PASSWORD')
)
The code tries to provide the correct values for your_username , your_password, your_host, and your_database. If the MySQL server is on the same machine as the Python code, you can use 'localhost' as the value for your_host. If the MySQL server is on a remote machine, make sure you provide the correct IP address or domain name.
The function execute_stored_procedure takes as arguments the connection to the database, the name of the stored procedure, and the values of the parameters. Inside the function, the callproc function is used to execute the stored procedure with the given parameters. Placeholders are indicated by %s in the placeholders string that is passed to the join function. The results are retrieved using the cursor's fetchall method and are returned as a list of tuples.
The usage example shows how you can use the execute_stored_procedure function to execute a stored procedure called sp_myprocedure with two parameters parameter_value1 and parameter_value2 . The results are printed to the console using the print function.
