How to prevent SQL injection in PHP?

SQL injection usually occurs when you ask a user for input, like their username/userid, and instead of a name/id, the user gives you an SQL statement that you will unknowingly run on your database. Like in the following example.

$unsafe_variable = $_POST['user_input'];

mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");

                                                                                                                                               

User can input something like " value'); DROP TABLE table;--, and the query becomes:

 

INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')

 

To make your application SQL injection proof always use prepared statements and parameterized queries. These are SQL statements that are sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.

Basically we have two options to achieve this:

Using PDO (for any supported database driver):

1.    $stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');

2.    $stmt->execute(array('name' => $name));

3.    foreach ($stmt as $row) {

4.       // do something with $row}

 

Using MySQLi (for MySQL):

1.    $stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');

2.    $stmt->bind_param('s', $name); // 's' specifies the variable type => 'string'

3.  $stmt->execute();

4.  $result = $stmt->get_result();

5.  while ($row = $result->fetch_assoc()) {

6.     // do something with $row}

 

If system connect to a database other than MySQL, there is a driver-specific second option refer (e.g. pg_prepare() and pg_execute() for PostgreSQL). PDO is the universal option.

 

Correctly setting up the connection

Note that when using PDO to access a MySQL database real prepared statements are not used by default. To fix this. To disable the emulation of prepared statements. An example of creating a connection using PDO is:

$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

 

In the above example the error mode isn't strictly necessary, but it is advised to add it. This way the script will not stop with a Fatal Error when something goes wrong. And it gives the developer the chance to catch any error(s) which are thrown as PDOException.

What is mandatory, however, is the first setAttribute() line, which tells PDO to disable emulated prepared statements and use real prepared statements. This makes sure the statement and the values aren't parsed by PHP before sending it to the MySQL server (giving a possible attacker no chance to inject malicious SQL).

Although user can set the charset in the options of the constructor, it's important to note that 'older' versions of PHP (< 5.3.6) silently ignored the charset parameter in the DSN.

Explanation

What happens is that the SQL statement to prepare is parsed and compiled by the database server. By specifying parameters (either a ? or a named parameter like :name in the example above) user tell the database engine where user want to filter on. Then call execute, the prepared statement is combined with the parameter values.

The important thing here is that the parameter values are combined with the compiled statement, not an SQL string. SQL injection works by tricking the script into including malicious strings when it creates SQL to send to the database. So by sending the actual SQL separately from the parameters, you limit the risk of ending up with something you didn't intend. Any parameters user send when using a prepared statement will just be treated as strings (although the database engine may do some optimization so parameters may end up as numbers too, of course). In the example above, if the $name variable contains 'Sarah'; DELETE FROM employees the result would simply be a search for the string "'Sarah'; DELETE FROM employees", and you will not end up with an empty table.

Another benefit of using prepared statements is that if we execute the same statement many times in the same session it will only be parsed and compiled once, giving some speed gains.

Oh, and since user asked about how to do it for an insert, here's an example (using PDO):

$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)');

$preparedStatement->execute(array('column' => $unsafeValue));



Was this article helpful?

Top Related Jobs

Top Freelance jobs are waiting for you, View & apply to earn more.

  • Python Developer

    $2,000, Fixed | India
    We are looking for brilliant Developer with a good English , dedicated to the projects and experienced with python,Django,php,Linux . Any additional s...
  • CorelDraw Automation

    $300, Fixed | Jordan
    We are looking to automate certain functions in CorelDraw.Details will be provided on the interview
  • Need more full-time developers to help with small projects.

    $500, Fixed | Romania
    We are looking to hire 10 new developers. The projects we will give you guys will range from $15-$500. It may be a simple 10-minute project like fixin...
  • Scripts & Utilities

    $60, Fixed | Romania
    We are running arpReach autoresponder script on our site. It was working fine with no problems and then we had to move to a new web host. After the mo...
  • Developer needed For a Cutom Portal development.

    $20, Fixed | Romania
    We need to design and integrate a new portal in our sub domain to a manage our team work. Portal will be used to assign work to team and submission fr...
  • Magneto 2 Extension Development

    $2,100, Fixed | Lithuania
    We require a Magento 2 extension based on an API, The Extension would consist of three parts. Catalog ServiceThird Party Viewer ServiceOrder Managemen...
  • PHP developer wanted for Implementation of Bitcoin Receive API

    $100, Fixed | Luxembourg
    We require a PHP developer to implement block.io API or blockcypher.com to enable us receive Bitcoin payment on our website. Any intending developer s...
  • Factory Outlet E-Commerce Project

    $14, Hourly | United Kingdom
    We seeking for dedicated Agency/Professional to work with us for a long term projectBeing able to work fast and efficiency, time is money, and we hate...


People also searched for

How to post a Job & Hire a great WordPress developer
How to post a Job & Hire a great WordPress develop...
Web Development

WordPress is an open source Content Management System (CMS), which allows the users to build dynamic websites and blog. WordPress is the most popular blogging system on the web and...

Read More
Some Useful JavaScript Tips,Tricks and Best Practices
Some Useful JavaScript Tips,Tricks and Best Practi...
Web Development

As you know, JavaScript is the top programming language in the world, the language of the web, of mobile hybrid apps (like PhoneGap or Appcelerator), of the server side (like NodeJ...

Read More
How to Write a Job Description to Find a Good PHP Developer?
How to Write a Job Description to Find a Good PHP...
Web Development

PHP (recursive acronym for PHP: Hypertext Pre-processor) is a widely-used open source general-purpose scripting language that is especially suited for web development and can be em...

Read More

You are freelancing Ninja?

Try Auto proposal today, The new virtual assistant for your freelancing world.