What is SQL injection? How can you prevent it?

SQL injection is a web hacking technique. It is the code injection technique which inserts malicious code into your database and destroys the database. This is the insertion of malicious code via web page input.

The main cause of SQL injection is providing data smartly into the SQL query which manipulates the data inside our database.

Suppose we have a table with students data. Each student can view his own data using his student id. The SQL query is designed such that it takes the student id input from the student.

Now, the student can enter his student id as “12345 or 1=1”. This translates into the followinq query.

SELECT * FROM Students WHERE id==12345 or 1=1

Now, the above query will return records of other students as well because 1=1 is always true. Hence, the data is of other students is not safe and prone to misuse by the hackers.

The Mysql connector module has method to escape query values in order to prevent SQL injection. The query values can be escaped using the placeholder %s.

Suppose, we have a table named “MyTable”.

+----------+---------+-----------+------------+
|    Name  | Class   |    City   |    Marks   |
+----------+---------+-----------+------------+
|    Karan |    4    | Amritsar  |    95      |
|    Sahil |    6    | Amritsar  |    93      |
|    Kriti |    3    | Batala    |    88      |
|   Khushi |    9    | Delhi     |    90      |
|    Kirat |    5    | Delhi     |    85      |
+----------+---------+-----------+------------+

Example

import mysql.connector

db=mysql.connector.connect(host="your host", user="your username", password="your
password",database="database_name")

cursor=db.cursor()

query="SELECT * FROM Students WHERE Name=%s"
name=("Karan",)

cursor.execute(query,name)

for row in myresult:
   print(row)

The above code shows the use of placeholders to escape query values.

Output

(‘Karan’, 4, ‘Amritsar’ , 95)