Protect your project from SQL injections
Tags: php, sql, xss, javascript
Published: 2022-02-10 12:14:19

In the following example I use a SQLite memory database to manage my vehicles...

**The user interface**
![The user interface](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hkkbp6ce9o6mrvzhvnyz.png)

**The code for this project...**

```php
<?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...**

![the filled form](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z3njbei96fals5ir3lxy.png)

**I get the following result:**


![the result](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dly0471ku2qtarov8jet.png)

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'`


![the filled form](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/22fnycrx3b48cptxi2z6.png)

**After submitting I get this result:**


![hacked result](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ixf1tf828yqatbb4mjjd.png)

**In real life, the data would now be gone, so it is advisable to work with prepared statements...**

**change this part to**
  
```php
$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:**

![sample XSS](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4kdiym0tit170uciv1wr.png)

**your result after submit:**


![xss result](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7no48f2vibroqwq6qp0j.png)

**Howto prevent:** 
  
first I write a simple function:

```php
function htmlOut ($val){
  return htmlspecialchars($val, ENT_COMPAT | ENT_HTML5, "UTF-8");
}
```

**and alter the tableFromArray function**  

```php
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:**

![result](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8897ghhylwbcq7u2m9wq.png)
**Good Luck**