Everything you need to know about SQL injection with PHP and MySQL

Everything you need to know about SQL injection with PHP and MySQL Thumbnail image
This is a work in progress. Maybe calling it 'everything you need to know about sql injection attacks' was a bit optimistic at this stage. But soon, after I publish the next update...

This is a guide for someone who isn't too sure about what SQL injection is.

It is a very important security consideration that every application or website should take into account.

A quick example of SQL injection attack is

(do not copy/paste this code. It is half php, half pseudo code, simplified to make things easier to understand

Let's say you have a blog (running on PHP, using MySQL or MariaDB for the database). You have a search box, where users can search for things. This is your code:

  1. <?php
  2. /** search.php **/
  3. // set up database connection
  4. $mysqli = new mysql("localhost","user","password","db");
  5.  
  6. echo "You searched for " . $_GET['search_for']."<br>";
  7.  
  8. // now do the search:
  9. $results = $mysqli->query(
  10.                "select   *
  11.                 from  blog_posts
  12.                 where  title = '" . $_GET['search_for'] . "'"
  13.               );
  14.  
  15. // tell the user how many rows (results) there were:
  16.  
  17. echo "We found " . $results->num_rows . " results";
  18. // now you can echo out the blog post data... (not shown)
  19. ?>

It is a simple example, using the MySQLi extension. In the real world, the search query would be more than just a simple where title = '...'.

If a user goes to http://localhost/search.php?search_for=videos (assuming the file above is /search.php), it will do a search for 'videos'.

The $_GET['search_for'] is the value of the ?search_for= in the URL (so in this case $_GET['search_for'] has the value of 'videos')

So the SQL statement that is sent is this (the user input value is underlined):

select * from blog_posts where title = 'videos'

This works fine, but what if a user searches for '; drop table blog_posts;?

Then the SQL statement(s) sent is the following:

select * from blog_posts where title = ''; drop table blog_posts; '

Which is now three statements:

  1. select * from blog_posts where title = '';
  2. drop table blog_posts;
  3. '

(The final SQL statement is an error, but it doesn't matter - we just want the 2nd statement to execute)

drop table foo will delete the database table foo.

I could have changed the drop to anything else. I could have done a select users.*, username as title, password as post_body from users and it would probably output the users/passwords as if they're blog posts.

How to protect yourself from SQL injection attacks

The simple answer to this is to use binded parameters/prepared statements. You send the SQL statement with placeholders for each value/param, and then send each parameter separately. For example, using MYSQLi (But please use PDO - a PDO example to follow):

  1. $stmt = $mysqli->prepare("INSERT INTO Countries  VALUES (?, ?, ?, ?)");
  2. $stmt->bind_param('sssi', $country_name, $language, $capital, $population);
  3. $stmt->execute();

(the sssd means string, string, string, integer - so $country_name, $language, $capital are interpreted as a string, but $population as an integer. This isn't important at this stage.)

By doing it with prepared statements it doesn't matter what $country_name is set to. It will always be protected from SQL injection.

As long as the SQL statement within the prepare() method doesn't have any user input then you are safe.

However, MYSQLi is a bit dated. It is fine to use, but if possible you should use PHP's PDO functions. PDO has better OOP support, and you can easily swap MySQL for a dozen other database drivers (SQLite, Postgres, etc).

The only reason I've found to use MySQLi over PDO is for asynchronous queries via mysqli:reap_async_query.

Ok, so how should you do prepared statements in PDO?

It is quite simple, and I think the easiest way is to just show by example. If you use prepared statements like this then you won't get affected by sql injections. Just make sure you don't use any user supplied input in the prepared SQL statements. This includes for things such as limits / offsets.

Connect to database with PDO in PHP

  1. // connect to database with pdo/php
  2. $options = [
  3.  // common default options
  4.   PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // fetch will return associative array
  5.   PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // errors will throw exceptions
  6. ];
  7. try {
  8.   $pdo = new PDO("mysql:host=localhost;dbname=YOURDBNAME;charset=utf8mb4", "USERNAME", "PASSWORD", $options);
  9. } catch (Exception $e) {
  10.   error_log($e->getMessage());
  11.   die("Sorry, but there was an error connecting to our database");
  12. }

How to do an insert with prepared statements in PHP/PDO

  1. // how to do an insert with prepared statements in php with pdo
  2. $stmt = $pdo->prepare("INSERT INTO cities  (city_name, country, population) VALUES (?, ?, ?)");
  3. $stmt->execute(["London","UK", 6000000]);
  4. echo $stmt->rowCount() . " rows were affected. The last ID was " . $pdo->lastInsertId();

How to select multiple rows with PDO in PHP

  1. // how to select rows with prepared statements in PHP with PDO
  2. $stmt = $pdo->prepare("SELECT * FROM cities WHERE country = ?");
  3. $stmt->execute(["UK"]);
  4. $results = $stmt->fetchAll(); // note the fetchAll()
  5. if(!$results) {
  6.    echo "No results for UK cities";
  7. }
  8. else{
  9.    echo "Results for UK Cities: " ;
  10.    print_r($results);
  11. }

How to select a single row with PDO in PHP

  1. // how to select single row
  2. $stmt = $pdo->prepare("SELECT *  FROM cities WHERE city_name = ?");
  3. $stmt->execute(["London"]);
  4. $row = $stmt->fetch(PDO::FETCH_ASSOC); // note the fetch()
  5. if ($row) {
  6.    echo "Result for city: London";
  7.    print_r($row);
  8. }
  9. else {
  10.   echo "No result for city search: London";
  11. }
webdevetc profile pic
webdevetc

I am a 29 year old freelance backend web developer from London, mostly focusing on PHP and Laravel lately. This is my site - I mostly write about PHP here. Contact me here (especially for any contracting jobs early 2019 in London ;) ).

Leave a Comment