Bulletproof SQL

Hi Everyone.

There are lots of websites here using PHP and MYSQL very nicely. When thinking of website’s security, preventing SQL injection is a must.

What is SQL injection?

SQL injection is a code injection technique that might destroy your database.
SQL injection is one of the most common web hacking techniques.
SQL injection is the placement of malicious code in SQL statements, via web page input.
(From bitdegree.org/learn/)

Take care of user input!!!

Lets think you have a php code like following.

<?php
$name = $_POST['name']; // get user input
$query = "SELECT * FROM users WHERE name = $name";
$mysqli -> query(query);

What if users input a name like

000webhost; DROP DATABASE DB1;

Then the query will be like

SELECT * FROM users WHERE name = 000webhost; DROP DATABASE DB1

It will definitely delete your database.

with this method user can access your whole database, actually. They can

  • Fetch passwords.
  • Delete records.
  • Delete tables.
  • Delete databses.
  • Get any information.
  • Actually everything related to DB.

How to prevent SQL Injection (Bulletproof SQL)

Let’s talk about php’s newest mysqli extension (OOP version). (I prefer you to use OOP mysqli)
It’s pretty simple to overcome this problem. Use prepared statements.

<?php
$mysqli = new mysqli(HOST,USER,PASSWORD,DB);

$name = $_POST['name'];
$query = "SELECT * FROM users WHERE name = ?";
$stmt = $mysqli -> prepare($query);
$stmt -> bind_param("s", $name);
$stmt -> execute();

// other php 

Explained

  • In the first line we connects to the database.
  • Then assigns user input to $name variable.
  • Declares the query. Mind the question mark. It’s is the value we send to the database server later.
  • Then we prepares the query.
  • Then the important part. We send the data to the DB server. We use bind_param function. First parameter must be the type of the variavle (s to string, i to integer) Then the variables respectively.
  • Then executes the query.

Visit this to learn more about PHP mysqli statements.

How does it work?

  • First php opens a statement to the DB server. (It creates DATABASE SESSIONS which is not important)
  • But data is null (the question marks)
  • Thereafter php sends the data to the DB saying “Hey hey, these are the data. Please don’t execute them as a SQL query. Take them as the data type i given in the first param of the bind_param function. :wink:
  • Then, executes it.

SQL injection is prevented!!!

Hope you enjoyed it and learnt something from it. If you have any questions to ask post here.

Mind that you can use php statements with PDO and mysqli procedural. but not with mysql.

Good luck with your website security. :smile:

3 Likes