How can I make this insertion secure from MySQL injection?

Question

I'm new to PHP and database programming and have been attempting to add data from a form to MySQL database. It works fine but is this open to my MySQL injection? I've read plenty of tutorials and I'm thinking PDO prepared statements. How can I do this for my comments field for example? This field (it's a text field) will be fairly open to whatever the user wants to put. How can I write this in order to make it more secure?

<?php
ob_start();
$username = 'name'; 
$password = 'pass'; 
$host = 'localhost'; 
$dbname = 'map';



try {
$dbh = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO Incidents (


        protocol,
        jurisdiction,
        date,
        time,
        comments,
        video,
        lat,
        lng



            )

        VALUES (


        '".$_POST["protocol"]."',
        '".$_POST["jurisdiction"]."',
        '".$_POST["date"]."',
        '".$_POST["time"]."',
        '".$_POST["comments"]."',
        '".$_POST["video"]."',
        '".$_POST["lat"]."',
        '".$_POST["lng"]."'


        )

        ";

// use exec() because no results are returned
$dbh->exec($sql);
header("Location: map1.php"); 

}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}

$dbh = null;




ob_end_flush();
?>

refer this : http://stackoverflow.com/questions/7043303/php-mysql-injection-protection — Insane Skull, Sep 07 '15 at 05:43

score 2 · Answer 1 · answered Sep 07 '15 at 05:46

You already use PDO and that is very good.

But, you should also use prepared statements witch should be SQL injection proof. Check this link for more info : http://php.net/manual/en/pdo.prepared-statements.php

An insert example from the doc :

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

score 2 · Accepted Answer · answered Sep 07 '15 at 05:54

PDO is already much secure. Now you can use bindParam() to increase security like:

<?php
 ob_start();
 $username = 'name'; 
 $password = 'pass'; 
 $host = 'localhost'; 
 $dbname = 'map';

try {
 $dbh = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
 // set the PDO error mode to exception
 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

 $sql = "INSERT INTO Incidents SET protocol = ?, jurisdiction = ?, date = ?, time = ?, comments = ?, video = ?, lat = ?, lng = ? ";
 $smt->$dbh->prepare($sql);
 $smt->bindParam(1, $_POST["protocol"]);
 $smt->bindParam(2, $_POST["jurisdiction"]);
 $smt->bindParam(3, $_POST["date"]);
 $smt->bindParam(4, $_POST["time"]);
 $smt->bindParam(5, $_POST["comments"]);
 $smt->bindParam(6, $_POST["video"]);
 $smt->bindParam(7, $_POST["lat"]);
 $smt->bindParam(8, $_POST["lng"]);

 // use exec() because no results are returned
 $smt->execute();
 if($smt->rowCount()) { // if insertion success
   header("Location: map1.php"); 
 }

}
catch(PDOException $e) {
   echo $sql . "<br>" . $e->getMessage();
}

 $dbh = null;

 ob_end_flush();
?>

PDO, if not done correctly, is no more secure than the mysql or mysqli APIs. — Mike, Sep 07 '15 at 05:57
**PDO is very easy to learn and to implement** as compare to **mysqli** Just open and go through the @cosmin 's link. — Shahzad Barkati, Sep 07 '15 at 06:10
The learning curve is pretty subjective. Some people are more comfortable with procedural style code and the old mysql_* functions and therefore the switch to mysqli instead of PDO would likely be a bit easier. — Mike, Sep 07 '15 at 06:12

score 0 · Answer 3 · answered Sep 07 '15 at 05:48

You should use prepare statement.

Well, do this:

new PDO("mysql:host=$host;dbname=$dbname", $username, $password);

$sql = 'SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour';

$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));

$sth->execute(array(':calories' => $variable, ':colour' => $color ));

$red = $sth->fetchAll();

$sth->execute(array(':calories' => 175, ':colour' => 'yellow'));

$yellow = $sth->fetchAll();

Example is originally taken from php.net

You can read more about it, here: http://php.net/manual/en/pdo.prepare.php

score 0 · Answer 4 · edited May 23 '17 at 11:58

0

1) Use PDO
2) Escape all the special characters
3) Use parameterized queries

refer this How can I prevent SQL injection in PHP?

edited May 23 '17 at 11:58

Community

1
1

answered Sep 07 '15 at 05:48

Wild Widow

2,359
3
22
34

1

Why would you escape the special characters? – Mike Sep 07 '15 at 05:53

How can I make this insertion secure from MySQL injection?

4 Answers4