[Database] SQL Injection

SQL injection is one of the most common web hacking techniques, and it usually occur when the website ask users for input, such as their user information. Users can enter some SQL commands as their information to change your database statement.

Example (with Python3 Django & PyMySQL)

import pymysql

def loginView(request):
  conn = pymysql.connect(xxx)
  cur = conn.cursor()

  user_name = request.POST.get("name")
  user_pwd = request.POST.get("password")

  sql = "SELECT * FROM users WHERE name='" + user_name + 
        "' AND password='" + user_pwd + "'"

  cur.execute(sql)

• If user input “mysql’ OR 1=1 -- ” as user name, then the SQL query will become as below.

SELECT * FROM users WHERE name='mysql' OR 1=1 -- AND password='xxx'

• The “--” will ignore the syntax after it, so users even no need to input the password.
• Users can retrieve data or modify the database statement without correct user information.

How to avoid

1. Limit the operation authority of the database for users.

2. Avoid to output the error message including SQL syntax.

3. Escape Parameters

• Use regular expression to check the input that users provide.
• Check input, and change the keyword in SQL to other legal characters.

4. Query Parameterization

import pymysql

def loginView(request):
  conn = pymysql.connect(xxx)
  cur = conn.cursor()

  user_name = request.POST.get("name")
  user_pwd = request.POST.get("password")

  sql = "SELECT * FROM users WHERE name=(%s) AND password=(%s)"
  val = (user_name, user_pwd)

  cur.execute(sql, val)

• SQL will prepare the command statement first, then wait for the corresponding parameters.
• The database knows exactly what this query will do, and this means the input can’t affect the query.
• If the input are illegal, then the statement will failed.