Prevention of SQL Injection

This explanation will provide an insight on what goes on behind the scenes, rather than copy and pasting queries that make no sense to you.

Part 1: What is SQLi??

SQLi is short for SQL Injection. It's caused by attackers sending remote SQLqueries to the web server. These attackers have an opening when strings aren't properly escaped.

First off, let's take a look at some vulnerable code.

First off, in order to SELECT information to be displayed on a page, you need to establish a connection with the MySQL server.

connect.php

<?php
$host = "127.0.0.1"; // MySQL Host
$user = "root";  // MySQL User
$pass = "";  // MySQL Password
$db = "PHP";  // MySQL Database

$login = mysql_connect($host,$user,$pass); // Login using the variables above.

if ($login) {  /* If login is successful*/
mysql_select_db($db) /*Select the database given above*/
or die(mysql_error()); /*Cancel the page, showing an error as to what went wrong*/
} else { /*Or Else*/
echo "Error!"; /*Display a custom error message*/
}


This code here is not vulnerable, its simply a connection to the database.

Now this part is where the backdoor comes in.

<?php
include('connect.php') // Include the login configuration.

if(isset($_GET['id'])) { // If the $_GET request 'id' is set
$id = $_GET['id']; // Set a variable for the GET request
$sql = "SELECT * FROM news WHERE id='$id'"; // Set a query to send from the database requesting the $_GET parameter.
$query = mysql_query($sql) or die(mysql_error()); // Send the query, or show an MySQL error if something went wrong.
while ($row = mysql_fetch_array($query)) { // Set a variable for the row result
echo $row['title']; // Display the row 'title' from the database on the page.
}
}
?>

Now that we have all that understood, the target site will data using the $_GET parameter.
For example.

http://localhost/PHP/news.php?id=

This will return the selected row in my PHP code above, WHERE my id parameter is 1. (id is the first column in my news table).
Now everything will be normal, the data will echo on to the page with no problem. However, this allows an attacker to execute remote SQL queries via the $_GET parameter (id) due to not being escaped properly.

For example...

http://localhost/PHP/news.php?id=1' AND (1)=(1)-- x

The is changes the SQL query to the following:
$sql = "SELECT * FROM news WHERE id='1' AND (1)=(1)-- x"; // String Based Blind SQL Injection


http://localhost/PHP/news.php?id=1' AND EXTRACTVALUE(RAND(),CONCAT('~',version()))-- x
The new query becomes the following:

$sql = "SELECT * FROM news WHERE id='1' AND EXTRACTVALUE(RAND(),CONCAT(0x7e,version()))-- x";
XPATH syntax error: '~5.5.36'


This is critical, as you can see it allows users to be able to select information from your database.

http://localhost/PHP/news.php?id=1' DIV(0) UNION SELECT 1,CONCAT_WS('~',username,password),3 FROM admin LIMIT 0,1-- x

The query becomes the following:

$sql = "SELECT * FROM news WHERE id='1' DIV(0) UNION SELECT 1,CONCAT_WS('~',username,password),3 FROM admin LIMIT 0,1-- x";
admin~lol123 


Now theres tons of ways to prevent SQLInjection. Heres probably one of the easiest ways, using mysql_escape_string

<?php
function sanitize($data){
$data = mysql_escape_string(trim($data));
return $data;
}

if (isset($_GET['id'])){
$id = sanitize($_GET['id']);
$sql = "SELECT * FROM news WHERE id='$id'";
$query = mysql_query($sql) or die(mysql_error());

while ($row = mysql_fetch_array($query)){
echo $row['title'];
}
}
?>


Now your parameter is properly escaped. Thanks for taking the time to read. Hope you guys learned a few things.