I really do not understand mysql injection? What is it?

Question

I am not experienced with mysql or php and i keep mentioning that in my questions but people keep saying you need mysql injection protection and I've looked it up and i really don't get it. Can anyone help me? I am so new to mysql and am having a bit of trouble with it

Here is my code:

How can it be improved? When i go to view my source code by right clicking on the site, none of the php/mysql appears.

<?php
$conn = mysql_connect("", "", "");
if (!$conn) {
  echo "Unable to connect to DB: " . mysql_error();
  exit;
}

$search = "%".$_POST["search"]."%";
$searchterm = "%".$_POST["searchterm"]."%";

if (!mysql_select_db("")) {
  echo "Unable to select mydbname: " . mysql_error();
  exit;
}

$sql = "SELECT name,lastname,email 
        FROM test_mysql
        WHERE name LIKE '%".$search."%' AND lastname LIKE '%".$searchterm."%'";
$result = mysql_query($sql);

if (!$result) {
  echo "Could not successfully run query ($sql) from DB: " . mysql_error();
  exit;
}

if(empty($_GET['search'])){ // or whatever your field's name is
  echo 'no results';
} else {
  performSearch(); // do what you're doing right now
}

if (mysql_num_rows($result) == 0) {
  echo "No rows found, nothing to print so am exiting";
  exit;
}
while ($row = mysql_fetch_assoc($result)) {
  echo '<br><br><div class="data1">';
  echo $row["name"];
  echo '</div><br><div class="data2">';
  echo $row["lastname"];
  echo '</div><br><div class="data3">';
  echo $row["email"];
  echo '</div>';
}
mysql_free_result($result);

?>

Answer 1

SQL injection is best summarised by this comic:

bobby-tables.com explains how to defend against it in various languages.

Answer 2

SQL injection is the name of the attack, but the underlying problem is insufficient input verification. Take the following code:

$search = $_POST['search'];
$sql = "SELECT name,lastname,email
FROM   test_mysql
WHERE  name LIKE '%".$search."%'";
$result = mysql_query($sql);

What happens if someone enters O'Reilly in the search form?

Well, the query is eventually constructed as:

SELECT name, lastname, email FROM test_mysql
WHERE name LIKE '%O'Reilly%'

This is not a valid SQL query, but will merely lead to an error message. Therefore, your code is buggy; it cannot handle inputs that contain '.

Now, let's consider a malicious person, Mallory. Causing the error doesn't help Mallory in his evil ways, unless he wants to stress database administrators who read all the error logs. He inputs: %'; INSERT INTO test_mysql name,lastname,email VALUES('mal','ory','malory@evil.com');--. Now, the complete SQL query is

SELECT name, lastname, email FROM test_mysql
       WHERE name LIKE '%%';
INSERT INTO test_mysql name,lastname,email
       VALUES('mal','ory','malory@evil.com');
--%'

The last line is a comment and ignored. Mallory can now write arbitrary things to the database!

Note that this requires the ability to execute more than one command in one MySQL. If that feature is not enabled, Mallory has to resort to using subqueries and predicates. In some cases, Mallory should not be able to view the whole table (for example, he should only be able to view his purchases in a webshop, not other customers'). He can simply input ' OR ''=' to see the whole content of the query.

You can protect yourself by either escaping values, like this:

$search = $_POST['search'];
$sql = "SELECT name,lastname,email
FROM   test_mysql
WHERE  name LIKE '%". mysql_real_escape_string($search) ."%'";
$result = mysql_query($sql);

Alternatively, use PDO and prepared statements:

$sql = "SELECT name,lastname,email
FROM   test_mysql
WHERE  name LIKE :search";
$statement = $pdo->execute($sql,
               array(':search' => '%' . $_POST['search'] . '%'));

Answer 3

You need to escape your queries with mysql_real_escape string:

WHERE  name LIKE '%".mysql_real_escape_string($search)."%' AND lastname LIKE '%".mysql_real_escape_string($searchterm)."%'";

You can see examples for SQL injection here: http://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php

Answer 4

Note that not only do you have an SQL-injection in your example code, but you also have an XSS security hole in there:

Code horror

while ($row = mysql_fetch_assoc($result)) {
  echo '<br><br><div class="data1">';
  echo $row["name"];
  echo '</div><br><div class="data2">';
  echo $row["lastname"];
  echo '</div><br><div class="data3">';
  echo $row["email"];
  echo '</div>';
}

You are echoing unsanitized output into a HTML page.
If a user has inputted html code in the name, lastname and/or email fields.
They can inject arbitrary html into your page.
This is called cross server site scripting or XSS.

You need to always sanitize the output that you echo like so:

Safe code

while ($row = mysql_fetch_assoc($result)) {
  echo '<br><br><div class="data1">';
  echo htmlentities($row["name"]);       <<-- sanitize your output!
  echo '</div><br><div class="data2">';
  echo htmlentities($row["lastname"]);
  echo '</div><br><div class="data3">';
  echo htmlentities($row["email"]);
  echo '</div>';
}

See: What are the best practices for avoiding xss attacks in a PHP site

Answer 5

You should specifically look at this line:

$sql = "SELECT name,lastname,email 
FROM   test_mysql
WHERE  name LIKE '%".$search."%' AND lastname LIKE '%".$searchterm."%'";

What are possible values that users could send in their post request to make this do something you do not want (like DROP the table!!)

This is what SQL injection is, and if not protected against, it can be very harmful.

Answer 6

SQL Injection is where the enduser is able to inject SQL code into your queries and mess with your databases.

For example, if you were running: UPDATE users SET userID=" + userID + " WHERE id = " + id + ";;

The user could put "; DROP TABLE users; as their input for the userID and SQL will run that query exactly. It can't detect a "bad" query.

You can protect yourself by using paramaterized queries, which there is multiple ways to do in PHP and you can just google "sql parameters php".