Bradley Bradley - 25 days ago 8
MySQL Question

Converting query to parametrised query

I have a query on my site and have recently been hacked because of it.

I have spent a good 2 hours looking how to convert this query so it is secure and have not got anywhere.

If anyone don't mind, could you please convert this one for me just so I can see what to do on the rest?

$camera_id = $_GET['camera_id'];

$cameras = mysqli_query($conn, "SELECT * FROM cameras WHERE id = $camera_id");
$camera = mysqli_fetch_array($cameras);

Answer

Try something like this.

$camera_id = $_GET['camera_id'];

$cameras = mysqli_prepare($conn, "SELECT * FROM cameras WHERE id = ?");
mysqli_stmt_bind_param($cameras, $camera_id);
$cameras->execute();

While you are making the switch, switch straight away to PDO. It's far better than mysqli

   $db = new PDO('mysql:host=localhost;dbname=mydb', 'username', 'password');
   $stmt = $db->prepare("SELECT * FROM cameras WHERE id = :camera_id");
   $stmt->execute(array(":camera_id"=>$camera_id));
   $result = $stmt->fetchAll();

or instead of fetchAll()

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo $row['field1'].' '.$row['field2']; //etc...
}

As you can see this is more readable. And if you later decide eto switch to postgresql the change is real easy.