Tutorials > Setting Up a Development Environment > Basic SQL Injection

8. Basic SQL Injection

• This article assumes you have completed the previous tutorials up to: Sample Website - Grocery List
• The three most common types of attacks on a website are: SQL injection, Cross-site scripting, and Cross-site request forgery.
• It is important to be aware of these and prevent them at all costs. A vulnerability can have devestating effects.
• The way a SQL injection attack is usually executed is through some sort of user input that is used in a database query. For instance, in the grocery list example, to remove an item, we created links to a URL that looked something like this:
  
  

  http://localhost/removeitem.php?item_name=Carrots

• And then in the code we would create our query like this:
  
  

  $query = "DELETE FROM groceries WHERE item_name = '".$_GET['item_name']."'";

• Which in the case of our example would produce this query (PHP does not have semi-colons at the end of queries):
  
  

  DELETE FROM groceries WHERE item_name = 'Carrots'

• What if instead of putting Carrots in the URL, we put something else, like (capitalization not required):
  
  

  ' OR '1' = '1

• Which would make our URL look like:
  
  

  http://localhost/removeitem.php?item_name=' OR '1' = '1

• Now instead of having Carrots in the query, we get this instead:
  
  

  DELETE FROM groceries WHERE item_name = '' OR '1' = '1'

• What this tells MySQL is to delete all items from the table groceries where column item_name is equal to "" (which isn't any) or "1" is equal to "1" (which will always be true). Therefore it will delete all rows from the database, and sure enough it does just that if you try entering that URL into your browser.
• And now when you go back to the main page there are no items.
• This isn't much of a setback for our example, but I'm sure you can see that this type of vulnerability could cause some serious damage.
• Okay great, so then how do we prevent such an attack? (source: xkcd.com)

  

• The best way to prevent SQL injection is using prepared statements. This isolates the inputs from the query. Here's how we would do it with removeitem.php:
  
  

  $mysqli = new mysqli("localhost", "root", "password", "test");
  $stmt = $mysqli->prepare("DELETE FROM groceries WHERE item_name = ?");
  $stmt->bind_params("s", $_GET['item_name']);
  $stmt->execute();

• What this does is uses the ? as a special character that signifies a single input and then binds the variable to the query separately. The "s" in bind_params() signifies a string variable. Here's how the whole file would look:
  
  

  <?php
  $mysqli = new mysqli("localhost", "root", "password", "test");
  $stmt = $mysqli->prepare("DELETE FROM groceries WHERE item_name = ?");
  $stmt->bind_params("s", $_GET['item_name']);
  if($stmt->execute()) {
  	echo "Item '".$_GET['item_name']."' removed from database.";
  } else {
  	echo "Problem removing item from database.";
  }
  $stmt->close();
  $mysqli->close();
  ?>
  <br/>
  <a href='/'>Back to grocery list</a>

  
• Now if you try going to the SQL injection URL we used earlier, we no longer have all of our items removed:
  
  

  http://localhost/removeitem.php?item_name=' OR '1' = '1

• Let's look at one more similar issue, try adding a new grocery item, Kellog's Raisin Bran. You should get an error.
• If we go in the code and echo our query out we'll see what's wrong:
  
  

  echo $query . "<br/>";

  
• This is the resulting query and you can see the apostrophe in Kellog's is breaking it:
  
  

  INSERT INTO groceries (item_name, in_cart) VALUES ('Kellog's Raisin Bran', 0)

• There are other ways to prevent this like using mysql_real_escape_string(), but PHP has already announced they will be deprecating these functions in favor of prepared statements.
• So if we update newitem.php to use prepared statements, we get:
  
  

  <?php
  $mysqli = new mysqli("localhost", "root", "password", "test");
  $stmt = $mysqli->prepare("INSERT INTO groceries (item_name, in_cart) VALUES (?, 0)");
  $stmt->bind_param("s", $_POST['item_name']);
  if($stmt->execute()) {
  	echo "Item '".$_POST['item_name']."' added to database.";
  } else {
  	echo "Problem adding item to database.";
  }
  ?>
  <br/>
  <a href='/'>Back to grocery list</a>

  
• And now we can add Kellog's Raisin Bran.
• You may have noticed there's one more problem. Clicking on the Remove item link doesn't work for items with an apostrophe. This is because of how we create the link in index.php:
  
  

  <a href='removeitem.php?item_name=<?php echo $row['item_name']; ?>'>Remove item</a>

• So, given our example, this code produces:
  
  

  <a href='removeitem.php?item_name=Kellog's Raisin Bran'>Remove item</a>

• Like before, the code can't tell the difference between the apostrophes used to start/end the URL and the ones used as part of a string. To fix this, HTML uses special URL characters. And in PHP we can easily do this using the urlencode() function.:
  
  

  <a href='removeitem.php?item_name=<?php echo urlencode($row['item_name']); ?>'>Remove item</a>

  
• This code will convert the item name into a string that is safe for URLs:
  
  

  <a href='removeitem.php?item_name=Kellog%27s+Raisin+Bran'>Remove item</a>

  

Return to Tutorials • Next Tutorial: Cross-site Scripting