How can I avoid SQL injection in my code below?

Question

I'm stuck thinking of how I can avoid SQL injection attacks on my code.

This is what I have now.

<?php
session_start();
$email = $_POST['e-mail'];
$fn = $_POST['firstname'];
$ln = $_POST['lastname'];
$cp = $_POST['cellphone'];
$phn = $_POST['phone_number'];
$comp = $_POST['company'];
$prov = $_POST['province'];
$brgy = $_POST['barangay'];
$fadd = $_POST['address'];
$sadd = $_POST['address2'];

$conn = mysqli_connect('localhost','root','','newcartdb')or die('Could not connect');

foreach($_POST['product'] as $product)
{
    $date = date('Y-m-d H:i:s');
    $order_name = $product['item_name'];
    $order_code = $product['item_code'];
    $order_qty = $product['item_qty'];
    $sub_total = $product['price'];

    $query = "INSERT INTO `newcartdb`.`orders`(`Email`,`Firstname`,`Lastname`,`ContactNum`,`PhoneNum`,`Company`,`Province`,`Barangay`,`FAddress`,`SAddress`,`ProductName`,`ProductCode`,`Qty`,`SubTotal`,`datetime`) VALUES('$email','$fn','$ln','$cp','$phn','$comp','$prov','$brgy','$fadd','$sadd','$order_name','$order_code','$order_qty','$sub_total','$date')";
    mysqli_query($conn,$query);
}



mysqli_close($conn);

header('Location: order_confirmation.php');
?>

How do I improve on this?

Why did you not use search facilities to find the information on your own? You use MySQLi, you use PHP, it is not hard to look for "php mysqli sql injection prevention". — N.B., Nov 10 '14 at 11:24

score 0 · Accepted Answer · edited Dec 17 '19 at 23:21

0

As suggested, prepared statements are the best way to achieve good protection from SQL injection.

Shortened Example

You will need to add entries to fill in all columns you wish to insert.

$email = $_POST['e-mail'];
$fn = $_POST['firstname'];
$ln = $_POST['lastname'];

if ($stmt = $mysqli->prepare("INSERT INTO `newcartdb`.`orders`(Email,Firstname,Lastname) values(?,?,?)") {
    $stmt->bind_param("sss",  $email, $fn, $ln);

"sss" - represents the data type i.e "s" - string, "i" - integer for each entry.

values(?,?,?) - this is a placeholder for the bind_params statement so the '?' will be replaced in sequential order with the values you place in the bind_params method

    $stmt->execute();

    $_SESSION['notice'] = "Table updated";
}

else{
    $_SESSION['notice'] = "Table could not be updated!";
}

edited Dec 17 '19 at 23:21

Dharman

30,962
25
85
135

answered Nov 10 '14 at 11:43

Stacker-flow

1,251
3
19
39

Sir what do you mean by (?,?,?). Do I need to put something in there? the variables or do I need to type it that way? :) – None other Nov 10 '14 at 11:47
With prepared statements the ? are placeholders, when the command is executed it will take the elements i.e("sss", $email, $fn, $ln); and replace the '?' in sequential order with the variables placed in the bind_params method, so no you do not need to replace that. – Stacker-flow Nov 10 '14 at 11:48
Ahhh. I got it. if I have 7 variables, then I must put 7 question mark in there right? Now I know why it's "?" for the hacker not to know it. :D I've learned today! wuhuu!!! Thank you sir @Stacker-flow. – None other Nov 10 '14 at 11:52
Sir sir!!! I'm curious about the 'sss'. :D is the number of 's' is depending on the number of variable? What if there is only one integer in the variable e.g $age,$name,$num. so it should be 'isi' it that right sir @Stacker-flow? – None other Nov 10 '14 at 12:04
Yes, the number of 's' in the first part must match (and be in order) of the columns that you are using. For example, (name, age, favourite_color) would have ..("sis", $name, $age, $color); – Stacker-flow Nov 10 '14 at 12:07
Sure sir. It helps me alot. Then the number of '?' is depending on how many is your variables right? :D – None other Nov 10 '14 at 12:18
That is correct, otherwise entries will not be included in the INSERT command – Stacker-flow Nov 10 '14 at 12:27
Ahhh! :D Sure sir @Stacker-flow. What about the other SQL commands? The Select, Delete and etc.? :D – None other Nov 10 '14 at 12:31
Have a look at the following http://php.net/manual/en/mysqli.quickstart.prepared-statements.php – Stacker-flow Nov 10 '14 at 12:33

How can I avoid SQL injection in my code below?

1 Answers1