{% extends 'core/Injections/injections.html' %}
{% load static %}

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
{% block inside-content %}
    <div class="bg-white nav-item flex-grow bg-opacity-20 backdrop-filter backdrop-blur-md w-4/5 problems-container">
         <h1 class="text-center text-white font-bold pb-5">Preventing SQLI</h1>

         <p class="text-white pb-3">
             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.
        </p>

        <h1 class="text-white font-bold pb-3">Preventing SQLI</h1>
        <p class="text-white">
            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.
            <br>Let's look at parametrization:
        </p>

        <div class="flex flex-grow pt-3 pb-3">
            <pre><code class="sql rounded-lg">sql_parameterized_query = """Update users set user = %u where id = %u"""</code></pre>
        </div>

        <p class="text-white">
             Here we can see an example of parametrized query call, with <span class="font-bold">%u</span> being variables holders
            into which we insert user input.<BR> Now let's see it as a whole, using python as an example.
        </p>

        <div class="flex flex-grow pt-3 pb-3">
            <pre><code class="python rounded-lg">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")</code></pre>
        </div>


        <p class="text-white">
            If you want to try this code out ,first install: <span
                class="font-bold">pip install mysql-connector-python</span>.
            <br><br>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.<br><br>

            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. <br><br>

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

        </p>

        <div class="flex flex-grow pt-3 pb-3">
            <pre><code class="python rounded-lg">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)</code></pre>
        </div>

    <p class="text-white">
             With result being:
    </p>

     <div class="flex flex-grow pt-3 pb-3">
            <pre><code class="python rounded-lg">  from users  username =admin or 1=1 and password=randompassword</code></pre>
     </div>

     <p class="text-white">
         After validation alone, the query is impossible to execute, and with an added layer of parametrization, our
         database will be safe.
    </p>

    </div>
{% endblock %}
</html>