In the following example I use a SQLite memory database to manage my vehicles...
The user interface
The code for this project...
<?PHP
function createMemoryDB () {
// First we need some data,
// I have some vehicles that need to be managed
$sqlCreate = <<<ENDSQL
CREATE TABLE myvehicles (id INTEGER PRIMARY KEY, vehicle TEXT, parkingspace TEXT);
INSERT INTO myvehicles (vehicle, parkingspace) VALUES
('Mercedes GLA', 'Garage 1 Place 11'),
('Mercedes SLK', 'Garage 2 Place 14'),
('Missile Launcher', 'Hall 2 Place 3'),
('Mercedes Vito', 'Garage 1 Place 12'),
('Bobbycar', 'Summer House Nizza'),
('Nissan Micra', 'Garage 1 Place 16')
;
ENDSQL;
$memDB = new PDO('sqlite::memory:');
$memDB->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$memDB->exec($sqlCreate);
return $memDB;
}
function tableFromArray($array){
$table = "<table><thead><tr>";
$header = array_keys($array[0]);
foreach ($header as $key){
$table .= '<th>'. $key;
}
$table.='</tr></thead><tbody>';
foreach($array as $key=>$row) {
$table.= "<tr>";
foreach($row as $key2=>$row2){
$table.= "<td>" . $row2 ;
}
$table.= "</tr>";
}
$table.= "</tbody></table>";
return $table;
}
// create DemoDB
$memDB = createMemoryDB();
$action = $_POST['action'] ?? "";
if ($action=== 'addcar'){
$vehicle = $_POST['vehicle'];
$parkingspace = $_POST['parkingspace'];
// insert into database
$result = $memDB->exec("
INSERT INTO myvehicles (vehicle, parkingspace) values ('$vehicle', '$parkingspace');");
}
$result = $memDB->query('SELECT vehicle, parkingspace FROM myvehicles');
if ($result){
$allVehicles = $result->fetchAll(PDO::FETCH_ASSOC);
}
if (sizeof($allVehicles)>0){
$myTable=tableFromArray($allVehicles);
} else {
$myTable='';
}
?>
<!DOCTYPE html>
<html lang="de">
<head>
<meta charset="UTF-8">
<title>SQL-Attack</title>
<link rel="stylesheet" href="https://unpkg.com/@picocss/pico@latest/css/pico.min.css">
<style>th{font-weight:bold;}</style>
</head>
<body>
<div class="container">
<hgroup>
<h1>my Vehicles</h1>
<h2>where can i find them...</h2>
</hgroup>
<hr>
<?=$myTable?>
<h3>add a new vehicle:</h3>
<form method="post">
<div class=container>
<h2>Fetch Data</h2>
<div class=grid>
<input type=hidden
name=action value='addcar'>
<label>Vehicle:
<input type=text name=vehicle
placeholder="vehicle" required>
</label>
<label>Parking Space:
<input type=text name=parkingspace
placeholder="parking space" required>
</label>
</div>
<button>Add new Vehicle</button>
</div>
</form>
</div>
</body>
</html>
When I fill out the form...
I get the following result:
Now I try the SQL injection, I fill out the form as follows:
Hackit'); DELETE FROM myvehicles;INSERT INTO myvehicles (vehicle, parkingspace) VALUES ('Hack' , 'Hackers Paradise'
After submitting I get this result:
In real life, the data would now be gone, so it is advisable to work with prepared statements...
change this part to
$action = $_POST['action'] ?? "";
if ($action=== 'addcar'){
$vehicle = $_POST['vehicle'];
$parkingspace = $_POST['parkingspace'];
// insert into database
// use prepared statments
$query = "INSERT INTO myvehicles (vehicle, parkingspace) values (:vehicle, :parkingspace);";
$myQuery = $memDB->prepare($query);
$prep = [
'vehicle'=>$vehicle,
'parkingspace'=>$parkingspace
];
$isOK = $myQuery->execute($prep);
}
now an XSS Attack:
your result after submit:
Howto prevent:
first I write a simple function:
function htmlOut ($val){
return htmlspecialchars($val, ENT_COMPAT | ENT_HTML5, "UTF-8");
}
and alter the tableFromArray function
function tableFromArray($array){
$table = "<table><thead><tr>";
$header = array_keys($array[0]);
foreach ($header as $key){
$table .= '<th>'. $key;
}
$table.='</tr></thead><tbody>';
foreach($array as $key=>$row) {
$table.= "<tr>";
foreach($row as $key2=>$row2){
$table.= "<td>" . htmlOut($row2) ;
}
$table.= "</tr>";
}
$table.= "</tbody></table>";
return $table;
}
your result after XSS Attack:
Good Luck
Top comments (1)
Thank you for code examples. This way it is impossible for an attacker to inject malicious SQL.
Indeed, the correct way to avoid SQL injection attacks is to use prepared statements and parameterized queries for any supported database driver. These are SQL statements that are sent to and parsed by the database server separately from any parameters.