php - How do I demonstrate a Second Order SQL Injection?

Question

So I've been trying to replicate a second order SQL Injection. Here's an example template of two php based sites that I've prepared. Let's just call it a voter registration form. A user can register and then you can check if you're a registered voter or not.

insert.php

<?php

$db_selected = mysql_select_db('canada',$conn);
if (!db_selected)
    die("can't use mysql: ". mysql_error());

$sql_statement = "INSERT into canada (UserID,FirstName,LastName,Age,State,Town)
                    values ('".mysql_real_escape_string($_REQUEST["UserID"])."',
                    '".mysql_real_escape_string($_REQUEST["FirstName"])."',
                    '".mysql_real_escape_string($_REQUEST["LastName"])."',
                    ".intval($_REQUEST["Age"]).",
                    '".mysql_real_escape_string($_REQUEST["State"])."',
                    '".mysql_real_escape_string($_REQUEST["Town"])."')";

echo "You ran the sql query=".$sql_statement."<br/>";
$qry = mysql_query($sql_statement,$conn) || die (mysql_error());
mysql_close($conn);
Echo "Data inserted successfully";
}
?>

select.php

<?php


$db_selected = mysql_select_db('canada', $conn);
if(!db_selected)
    die('Can't use mysql:' . mysql_error());
$sql = "SELECT * FROM canada WHERE UserID='".addslashes($_POST["UserID"])."'";
echo "You ran the sql query=".$sql."<br/>";
$result = mysql_query($sql,$conn);
$row=mysql_fetch_row($result);

$sql1 = "SELECT * FROM canada WHERE FirstName = '".$row[1]."'";
echo "The web application ran the sql query internally=" .$sql1. "<br/>";
$result1 = mysql_query($sql1, $conn);
$row1 = mysql_fetch_row($result1);

mysql_close($conn);
echo "<br><b><center>Database Output</center></b><br><br>";

echo "<br>$row1[1] $row1[2] , you are a voter! <br>";

echo "<b>VoterID: $row[0]</b><br>First Name: $row[1]<br>Last Name: $row[2]
    <br>Age: $row[3]<br>Town: $row[4]<br>State: $row[5]<br><hr><br>";
}
?>

So I purposely made this vulnerable to show how second order SQL Injection works, a user can type in a code into the first name section (where I am currently stuck, I've tried many different ways but it seems that I can't get it to do anything). Then when a person wants to activate the code that he has inserted in the first name section, all he needs to do is just type in the userID and the code will be inserted.

For example: I will type into the insert.php page as: userid = 17

firstname = (I need to inject something here)

lastname = ..

age = ..

town = ..

state = ..

Then when I check for my details, and type in 17, the SQL script injected will be activated. Can I get few examples on what sort of vulnerabilities I can show through this?

Answer 1

What is there to demonstrate?

Second order SQL injection is nothing more than SQL injection, but the unsafe code isn't the first line.

So, to demonstrate:

1) Create a SQL injection string that would do something unwanted when executed without escaping.

2) Store that string safely in your DB (with escaping).

3) Let some other piece of your code FETCH that string, and use it elsewhere without escaping.

EDIT: Added some examplecode:

A table:

CREATE TABLE tblUsers (
  userId serial PRIMARY KEY,
  firstName TEXT
)

Suppose you have some SAFE code like this, receiving firstname from a form:

$firstname = someEscapeFunction($_POST["firstname"]);

$SQL = "INSERT INTO tblUsers (firstname) VALUES ('{$firstname }');";
someConnection->execute($SQL);

So far, so good, assuming that someEscapeFunction() does a fine job. It isn't possible to inject SQL.

If I would send as a value for firstname the following line, you wouldn't mind:

bla'); DELETE FROM tblUsers; //

Now, suppose somebody on the same system wants to transport firstName from tblUsers to tblWhatever, and does that like this:

$userid = 42;
$SQL = "SELECT firstname FROM tblUsers WHERE (userId={$userid})";
$RS = con->fetchAll($SQL);
$firstName = $RS[0]["firstName"];

And then inserts it into tblWhatever without escaping:

$SQL = "INSERT INTO tblWhatever (firstName) VALUES ('{$firstName}');";

Now, if firstname contains some deletecommand it will still be executed.