Prevent SQL Injection in PHP

SQL injection is one of the most common vulnerabilities in applications on the web today. This article will show you how to 100% prevent SQL injection on your website using Prepared Statements in PHP.

Steps

What is SQL Injection?

SQL Injection is a type of vulnerability in applications that use an SQL database. The vulnerability arises when a user input is used in a SQL Statement.

Like Below:

As you can see the value the user enters into the URL variable username will get assigned to the variable $name and then placed directly into the SQL statement. This means that is possible for the user to edit the SQL statement.

The SQL database will then receive the SQL statement as the following:

Which is valid SQL, and instead of returning one password for the user, the statement would return all the passwords in the table tbl_user. This is not something anyone wants in their web applications. This article will show you how to prevent this type of vulnerability.

Use Prepared Statements

To prevent SQL injections we will have to use something called prepared statements which uses bound parameters. Prepared Statements do not combine variables with SQL strings, so it is not possible for an attacker to modify the SQL statement. Prepared Statements combine the variable with the compiled SQL statement, this means that the SQL and the variables are sent separately and the variables are just interpreted as strings, not part of the SQL statement.

Prepared Statements with mySQLi.

Using the methods in the steps below, you will not need to use any other SQL injection filtering techniques such as mysql_real_escape_string(). This is because with prepared statements it is not possible to do conventional SQL injection.

  1. mySQLi SELECT Query.
    The below script is how to SELECT data from a table using mySQLi Prepared Statements.



    Note: The variable $mysqli is the mySQLi Connection Object.

  2. mySQLi INSERT Query.
    The below script is how to INSERT data into a table using mySQLi Prepared Statements.



    Note: The variable $mysqli is the mySQLi Connection Object.



  3. mySQLi UPDATE Query.
    The below script is how to UPDATE data in a table using mySQLi Prepared Statements.



    Note: The variable $mysqli is the mySQLi Connection Object.



  4. mySQLi DELETE Query.
    The below script is how to DELETE data from a table using mySQLi Prepared Statements.



    Note: The variable $mysqli is the mySQLi Connection Object.



Further Reading

Related Articles

Retrieved from "https://kipkis.com/index.php?title=Prevent_SQL_Injection_in_PHP&oldid=282580"