{% extends 'core/Injections/injections.html' %} {% load static %} Title {% block inside-content %}

Preventing SQLI

My advice for preventing SQLI is to use already protected frameworks or have libraries to stop the SQL injections. This website made by Django has build protection. However, frameworks, more often than not, are not SQLI-resistant by default, so let's see how to do it ourselves.

Preventing SQLI

An intelligent and almost always used method to avoid SQLIs is parametrization. It is a method of pre-compiling a SQL-statement into which only parameters ("variables") are supplied, to be then executed. The preprocessing is done so the database knows what query type is being used, such as a SELECT query will only be a SELECT query, no matter the users' input. Data is injected as a string into a variable. This way, we avoid direct access to the database and separate user input from back-end queries. We can add another layer, input validation, and stored procedures to strengthen the security.
Let's look at parametrization: 

sql_parameterized_query = """Update users set user = %u where id = %u"""

Here we can see an example of parametrized query call, with %u being variables holders into which we insert user input.
Now let's see it as a whole, using python as an example. 

import mysql.connector

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='myDatabase',
                                         user='root')

    cursor = connection.cursor(prepared=True)

    sql_insert_query = """ INSERT INTO User
                       (id, username, email, password) VALUES (%s,%s,%s,%s)"""

    data = (1, "Michael", "michael@gmail.com", verySecurePassword123)

    cursor.execute(sql_insert_query, data)
    connection.commit()

except mysql.connector.Error as error:
    print("parameterized query failed {}".format(error))
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("Closing MySql connection")

If you want to try this code out ,first install: pip install mysql-connector-python.

Let's dissect this code a little. First, we need to create a connection with our database. Once that is done, the cursor is initiated. Cursor is used to communicate with the MySQL database. With it, we can execute SQL statements, fetch result data, and call procedures.

After that, we create the query itself and define what data and in which way we want to insert them. In the example tuple is used, which could be the input parameter of the function. It can, however, be switched to what suits your implementation the most. Then we execute the query, using the cursor, and commit the changes.

Then we catch any errors that may occur and close the cursor and database connection.
To add security, we can validate user input, such as replacing unwanted characters or searching for unwanted words.
For example: 

username = "SELECT * FROM users Where username ='admin' OR '1'='1' AND password='randompassword'"

username = username.lower()

replacements = [
    ("'", ""),
    ("select", ""),
    ("update", ""),
    ("delete", ""),
    ("where", ""),
    ("*", ""),
]

for left, right in replacements:
    username = username.replace(left, right)

With result being: 

  from users  username =admin or 1=1 and password=randompassword

After validation alone, the query is impossible to execute, and with an added layer of parametrization, our database will be safe.

{% endblock %}