[psycopg2] SQL Injection and Ways to defened it

1. What is SQL Injection?

SQL Injection is a method for code injection attack that intentionally exploits application security loopholes to cause malicious SQL statements to be executed, resulting in abnormal manipulation of the database.

 

query = "SELECT * FROM users WHERE username = '" + name + "';"

 

The above code selects a row that receives an ID and password from a user and matches it. However, if an attack enters "admin OR 1 = 1; --" in the unername, the query statement is as follows.

 

SELECT * 
  FROM users 
 WHERE name = 'admin' OR 1 = 1; --

 

Since 1 = 1 is always true, it allows an attacker to log in without entering a password. Some SQL injection examples are :

 

• Adding a boolean to a where clause that is always true like ' OR 1=1
• Escaping part of query by entering line comments --
• Ending the initial query and start a new query '; DROP TABLE USERS;
• Connecting data from multiple tables by using UNION

 

2. How to escape from SQL Injection?

Postgres offers two mechanisms to prevent SQL injections. One is using placeholders as the parameter of execute function, the other is prepared statement.

 

2.1 %s Placeholder

If there is more than one value to be added, the value can be added using Python's string formatting method. The cursor.execute() function can add a value to the table by receiving a query statement including a placeholder and a replacement value. There are two possible placeholders inside the function.

 

• %s : Placeholder to replace values in order.
• %(key)s : Placeholder to replace the value according to the key value.

 

The second method should use dictionary that exist in the form of columns : values instead of tuple or list.

 

import psycopg2
conn = psycopg2.connect("dbname=db user=ur")
cursor = conn.cursor()

# Way of use %s for placeholder 
query = "INSERT INTO users VALUES (%s, %s, %s, ...);" 
cursor.execute(query, ('val1', 'val2', ...))

# Way of user %(key)for placeholder
row_dict = {
    'key1' : 'val1',
    'key2' : 'val2',
    ...
}   
query = "INSERT INTO users VALUES (%(key1)s, %(key2)s, ...);"
cursor.execute(query, row_dict)

 

2.2 Prepared Statement

There is a way to defend against SQL injection attacks using Prepared Statement. By using prepared statement, additional SQL statements can be ignored and queries based on their original purpose can be performed.

 

In other words, prepared satement is a concept such as a predefined function to defend SQL injection, and it specifies the type and format of the variable to prevent additional SQL queries from being peformed. Once the prepared statement is defined, it is pefromed through the EXECUTE clause.

 

# using string formatting 
cursor.execute("SELECT * FROM table WHERE ... %s;", (val1,))

# Make Prepare Statement in SQL
"""
prepared_statement_name(type1, type2, type3) AS 
    Intended SQL statemtns
""" 

cursor.execute("""
	PREPARE pre_name(type1, type2, type3, ...) AS
        INSERT INTO table VALUES($1, ..., $n) 
""")
cursor.execute("EXECUTE pre_name(%s, %s, ...);", (val1, val2, ...))

 

Once the prepared statement is created, the name, format, and data type are stored in pg_prepared_statement table. And the generated prepared statement disapeears when the connection is closed, preventing other databsae users from using it. The prepared statement is time efficient for repreated query statements because the format is defined once.

 

 

Source from :

• https://snyk.io/blog/sql-injection-cheat-sheet/  
• https://www.postgresql.org/docs/12/view-pg-prepared-statements.html