45886

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:<br />

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:<br />

<blockquote>

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

</blockquote>

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

Answer1:

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)");

<strong>EDIT:</strong>

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

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

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

Answer2:

In addition to <a href="https://stackoverflow.com/a/40248987/3536236" rel="nofollow">useyourillusiontoo's</a> 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 <strong>in the order they appear in the SQL string</strong>, 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 <strong>must</strong> match.

<blockquote>

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

</blockquote>

You will need to <a href="https://stackoverflow.com/questions/16612251/how-to-bind-multiple-parameters-to-mysqli-query" rel="nofollow">read up a bit</a> on the general syntax differences of MySQLi and PDO and particularly about what <a href="https://stackoverflow.com/a/26826585/3536236" rel="nofollow">the first paramter</a> 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 <em>not</em> guarentee you can also run PDO. They're different. You can read more about correct PDO setup in your script <strong><a href="https://stackoverflow.com/questions/11369360/how-to-properly-set-up-a-pdo-connection" rel="nofollow">here</a></strong>.

Recommend

  • LibGit2Sharp and Authentication UI
  • Integrating Django and .Net applications using Single Sign On (SSO)
  • KoGrid JSON Dynamic widgets, with nested server calls
  • Bundle install fails - Gem using github url - What is wrong?
  • Doctrine: UniqueEntity with Many-to-Many field
  • Access 2007 forms with parameterized RecordSource
  • Cleave.js Phone CA
  • Zend Framework 2, Module Redirect
  • jQuery file download plugin
  • Why does PHP appear to evaluate this condition incorrectly?
  • Understanding Intl.DateTimeFormat as a JavaScript object
  • Contact form problem - I do receive messages, but no contents (blank page)
  • Error processing multiple files
  • Security issues with PHP's Readfile method
  • Eloquent paginate function in Slim 3 project using twig
  • onBackPressed() not being executed
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • Can I check if a recipient has an automatic reply before I send an email?
  • Optimizing database types to compact database (SQLite)
  • TFS: Get latest causes slow project reloading
  • Running a C# exe file
  • Join two tables and save into third-sql
  • How to model a transition system with SPIN
  • Display Images one by one with next and previous functionality
  • ORA-29908: missing primary invocation for ancillary operator
  • Delete MySQLi record without showing the id in the URL
  • SQL merge duplicate rows and join values that are different
  • Hits per day in Google Big Query
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • Linking SubReports Without LinkChild/LinkMaster
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass
  • Programmatically clearing map cache
  • costura.fody for a dll that references another dll
  • Reading document lines to the user (python)
  • Observable and ngFor in Angular 2
  • UserPrincipal.Current returns apppool on IIS
  • Converting MP3 duration time
  • To Get the radio button value in ruby on rails
  • java string with new operator and a literal