SQL Injection: trying to avoid it but got an error

Question

So this is only a part of my code but the only relevant thing:

if ($check == 0) {     
                    $host = "localhost";
                    $user = "root";
                    $pass = "";
                    $db = "myfirstdb";
                    $connect = new mysqli($host,$user,$pass,$db);
                    if ($connect->connect_error){ 
                        die("Connection failed: " . $connect->connect_error);
                    } else {
                        echo "Connected successfully!";
                    }

                    //$sql = "INSERT INTO table1 (firstname , lastname , phone , email , date) VALUES (:fname, :lname, :phone, :email, :date)";
                    $secure = $db->prepare("INSERT INTO table1 (firstname , lastname , phone , email , date) VALUES (:fname, :lname, :phone, :email, :date)");
                    $secure->bindParam(':fname' , $firstname);
                    $secure->bindParam(':lname' , $lastname);
                    $secure->bindParam(':phone' , $phone);
                    $secure->bindParam(':email' , $email);
                    $secure->bindParam(':date' , $date);
                    $secure->execute();
                    /*if ($connect->query($sql) === TRUE) {
                        echo "New record created successfully";
                    } else {
                        echo "Error: " . $sql . "<br>" . $connect->error;
                    }*/

                    $connect->close();

The problem i have is whenever i execute the code an error pops out:

Fatal error: Uncaught Error: Call to a member function prepare() on string in C:\xampp\htdocs\example\Index.php:206 Stack trace: #0 {main} thrown in C:\xampp\htdocs\example\Index.php on line 206

I'm trying to avoid the SQL injection by using this code but I'm not sure whether I understood it.

Your prepare failed, you failed to check for failure, and propagated the error onwards. specifically: mysqli doesn't support named placeholders. You're trying to use PDO semantics with a completely different library. Never **EVER** assume success with operations on an external resources. Always assume failure, check for failure, and treats sucess as a pleasant surprise. — Marc B, Oct 25 '16 at 20:12
also I don't see where the variables you're passing in were set. — xQbert, Oct 25 '16 at 20:13
The variables were set but I didn't paste the whole code as I was thinking it wasn't relevant. — Whitewolf3131, Oct 25 '16 at 20:17
@MarcB According to [php mysqli](http://php.net/manual/tr/mysqli.prepare.php) it is not PDO semantics only. Mysqli can be used like this. — Hakan SONMEZ, Oct 25 '16 at 20:18
@HakanSONMEZ: mysqli supports prepared statements. it does **NOT** support named placeholders. `foo=?` is ok in mysql. `foo=:bar` is not. — Marc B, Oct 25 '16 at 20:18
@MarcB Thank you. I put "?" instead of ":foo" and it works by replacing bindParam() with bind_param and $connect instead of $db. (I also watched a w3schools tutorial to understand better the prepare() in PDO) — Whitewolf3131, Oct 25 '16 at 20:39

score 2 · Answer 1 · edited Oct 25 '16 at 20:26

You aren't preparing the statement on the correct variable. You need to do:

$connect->prepare("INSERT INTO table1 (firstname , lastname , phone , email , date) VALUES (:fname, :lname, :phone, :email, :date)");

EDIT:

  $db = "myfirstdb";
  $connect = new mysqli($host,$user,$pass,$db);

Your Object is the vaiable you set as a "new class", so in this case your object is $connect which is a new mysqli class instance. Your original script (causing the error) was using the $db variable which is a string not an Object.

You can only ->prepare (and use the -> syntax) on Objects .

score 1 · Answer 2 · edited May 23 '17 at 12:19

In addition to useyourillusiontoo's answer, and as Marc B pointed out in comments, the code you display is confused between MySQLi and PDO.

There are various differences, you're basically trying to fit a hexagon into a Septagon shaped hole. MySQLi uses ? as placeholders, and PDO uses named placeholders as you have in your script.

For example your code is:

 $connect = new mysqli($host,$user,$pass,$db);

This means you're using the mysqli DB handler, so you need to replace your :placeholder with ? and then set the variables in the order they appear in the SQL string, such as:

   $secure = $connect->prepare("INSERT INTO table1 
                    (firstname , lastname , phone , email , date)
                    VALUES (?, ?, ?, ?, ?)");
   $secure->bind_param('sssss' , 
                      $firstname, $lastname , $phone, $email ,$date);

So the first ? takes the first variable after the variable type declaration (The set of sess sssss), so first ? refers to $firstname in this instance. The number of ? and the number of variables given in the bind parameter must match.

Note the class method is ->bind_param for MySQLi rather than ->bindParam.

You will need to read up a bit on the general syntax differences of MySQLi and PDO and particularly about what the first paramter on the bind_param means .

To use PDO in your script you would set your class as:

$connect = new PDO(...$details...);

I would qualify that if you can use MySQLi this does not guarentee you can also run PDO. They're different. You can read more about correct PDO setup in your script here.

SQL Injection: trying to avoid it but got an error

2 Answers2

Linked