Proper way to create PHP prepared statement?

Question

I am new to PHP

Currently working on building my 1st web app!

I created this PHP script for registering / signing up for my web app, and I want it to prevent SQL injection So I need some guideance here, I learned all by my self from ground zero!

So I decided to use from what I have read and learned.

Here's my code:

$server = "localhost";
$user = "root";
$pass = "";
$selected_db = "User";
$selected_table = "usersbio";

// Create connection
$linking = new mysqli($server, $user, $pass, $selected_db);

// Input variable
$firstname = mysqli_real_escape_string($linking, $_POST['firstname']);
$lastname = mysqli_real_escape_string($linking, $_POST['lastname']);
$userpass = mysqli_real_escape_string($linking, $_POST['userpass']);
$useremail = mysqli_real_escape_string($linking, $_POST['useremail']);
$udob_d = mysqli_real_escape_string($linking, $_POST['userdobd']);
$udob_m = mysqli_real_escape_string($linking, $_POST['userdobm']);
$udob_y = mysqli_real_escape_string($linking, $_POST['userdoby']);

$hashingpass = password_hash($userpass, PASSWORD_DEFAULT);

// Saving data to db - table
$stmt = $linking->prepare("INSERT INTO $selected_table (userfirstname, 
userlastname, userpasskey, useremail, userdobd, userdobm, userdoby) 
VALUES ('?', '?', '?', '?', '?', '?', '?')");

$stmt->bind_param('s', 's', 's', 's', 'i', 's', 'i', $firstname, 
$lastname, $hashingpass, $useremail, $udob_d, $udob_m, $udob_y);
$stmt->execute();

$linking->close();

Please post your code in here, not as link to an external image. — Jeff, Nov 04 '18 at 17:14
Don't rely on the `real_escape_string()` functions to prevent SQL injection, [they alone are not sufficient](https://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string). You should use prepared statements with bound parameters, via either [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php). [**This post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has some good examples. — Alex Howansky, Nov 04 '18 at 17:15
The [bind_param](http://php.net/manual/en/mysqli-stmt.bind-param.php) is wrong. The types should be in _one_ string as first param: 'ssiss...'. — Jeff, Nov 04 '18 at 17:15
@jeff, that is the thing I don't know this 'ssiss' thing really confuse me — Gwein, Nov 04 '18 at 17:20
@ChristianDelvianto Did you click the link in Jeff's comment? Look for the examples and compare it to yours. — Ivar, Nov 04 '18 at 17:26
@ChristianDelvianto just paste the code in there and I can format it for you! — , Nov 04 '18 at 17:33
Do yourself a favour and use PDO instead. Mysqli is just there to make code more long-winded. — mario, Nov 04 '18 at 17:56
You already use prepared statements, in this case you should **not** call `real_escape_string()` beforehand. There is no need to escape anything, because the database gets the SQL statement and the user input separately and does not have to extract the user input from the SQL statement. — martinstoeckli, Nov 05 '18 at 07:39

score 2 · Answer 1 · answered Nov 04 '18 at 18:14

If you're just getting started, I'd suggest using PDO instead of the old mysqli interface. It's much less verbose and easier to use. Here's how your code would look:

<?php
$server = "localhost";
$user = "root";
$pass = "";
$selected_db = "User";

// Create connection
$linking = new PDO("mysql:host=$server;dbname=$selected_db", $user, $pass);

// Saving data to db - table
$query = "
    INSERT INTO usersbio
    (userfirstname, userlastname, userpasskey, useremail, userdobd, userdobm, userdoby)
    VALUES (?, ?, ?, ?, ?, ?, ?)";

$params = [
    $_POST["firstname"],
    $_POST["lastname"],
    password_hash($_POST["userpass"], PASSWORD_DEFAULT),
    $_POST["useremail"],
    $_POST["userdobd"],
    $_POST["userdobm"],
    $_POST["userdoby"],
];
$stmt = $linking->prepare($query);
$stmt->execute($params);

$linking->close();

score -1 · Accepted Answer · edited Nov 04 '18 at 18:10

You prepare() statement looks like it should work...

Also as Alex Howansky said:

Don't rely on the real_escape_string() functions to prevent SQL injection, they alone are not sufficient. You should use prepared statements with bound parameters, via either mysqli or PDO. This post has some good examples.

Refer to: How can I prevent SQL injection in PHP?

You also have to remove the the single quotes around the question marks...

TRY THIS:

$server = "localhost";
$user = "root";
$pass = "";
$selected_db = "User";
$selected_table = "usersbio";

// Create connection
$linking = new mysqli($server, $user, $pass, $selected_db);

// Input variable
$firstname = mysqli_real_escape_string($linking, $_POST['firstname']);
$lastname = mysqli_real_escape_string($linking, $_POST['lastname']);
$userpass = mysqli_real_escape_string($linking, $_POST['userpass']);
$useremail = mysqli_real_escape_string($linking, $_POST['useremail']);
$udob_d = mysqli_real_escape_string($linking, $_POST['userdobd']);
$udob_m = mysqli_real_escape_string($linking, $_POST['userdobm']);
$udob_y = mysqli_real_escape_string($linking, $_POST['userdoby']);

$hashingpass = password_hash($userpass, PASSWORD_DEFAULT);

// Saving data to db - table
$stmt = $linking->prepare("INSERT INTO $selected_table (userfirstname, 
userlastname, userpasskey, useremail, userdobd, userdobm, userdoby) 
VALUES (?, ?, ?, ?, ?, ?, ?)");

$stmt->bind_param('ssssisi', $firstname, 
$lastname, $hashingpass, $useremail, $udob_d, $udob_m, $udob_y);
$stmt->execute();

$linking->close();

THIS IS THE UPDATED QUERY:

INSERT INTO $selected_table (userfirstname, userlastname, userpasskey, useremail, userdobd, userdobm, userdoby)
VALUES (?, ?, ?, ?, ?, ?, ?)

If you need more help with prepared statements take a look at this link:

http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

Yes I read that, because of that I decided to use prepared statement — Gwein, Nov 04 '18 at 17:38
I change 's', 's', 's', 's', 'i', 's', 'i' to 'ssssisi' And I got this error : Warning: mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement — Gwein, Nov 04 '18 at 17:52
@ChristianDelvianto go ahead and try again I have updated my answer — , Nov 04 '18 at 17:53
@ChristianDelvianto can you accept there is a check right under where the downvotes and upvotes you click that and that is how you accept! — , Nov 04 '18 at 18:10
done, haha, many thanks to all review this newbie post of mine — Gwein, Nov 04 '18 at 18:13

Proper way to create PHP prepared statement?

2 Answers2