Post data security in PHP using prepared statement

Question

I am using a prepared statement for submitting the form using ajax. I tried the below code which is working. I need to know some securities. I checked on google and found some answers and made the below code.

I want to know my post data is correct? Do I need to FILTER_SANITIZE_STRING?

I entered <h1>naren</h1> and naren's and submitted. Below is database output.

Database output

I got slash (/) and apostrophy in the last row.

Process.php

function register($conn){
    global $currentdate;
    $name=$conn->real_escape_string(trim($_POST['name']));
    $country=$conn->real_escape_string(trim($_POST['country']));
    $mobileno=$conn->real_escape_string(trim($_POST['mobileno']));
    $email=$conn->real_escape_string(trim($_POST['email']));

    if($name == "") {
        $errorMsg="Name field is required";
        $code="1";
    } else if($country == "") {
        $errorMsg="Country field is required";
        $code="2";
    } elseif ($mobileno=="") {
        $errorMsg="Mobile number is required";
        $code="3";
    } elseif (is_numeric(trim($mobileno))==false) {
        $errorMsg="Only contain a number";
        $code="3";
    } elseif (strlen($mobileno)<10) {
        $errorMsg="Contain minimun 10 number ex:9892555555";
         $code="3";
    } elseif (strlen($mobileno)>10) {
        $errorMsg="Contain maximum 10 number ex:9892555555";
        $code="3";
    } elseif ($email =="") {
        $errorMsg="Email filed is required";
        $code="4";
    } elseif (!preg_match("/^[_\.0-9a-zA-Z-]+@([0-9a-zA-Z][0-9a-zA-Z-]+\.)+[a-zA-Z]{2,6}$/i", $email)) {
        $errorMsg="Please enter valid email id";
        $code="4";
    } else {
        $query="INSERT INTO `register` (name, country, mobileno, email ,date_of_added) VALUES (?,?,?,?,?)";

        if($stmt = $conn->prepare($query)) {
            $stmt->bind_param("sisss", 
                                $name,$country,$mobileno,
                                $email,$currentdate);
            $stmt->execute();
            $errorMsg="Data Inserted";
            $code="5";
            $_SESSION['thankyouSession'] = "true";
        }else{
            $code= "6";
            $errorMsg='Something is wrong';
        }

        $stmt->close();
        $conn->close();
    }
    $response['error']=$errorMsg;
    $response['error_no']=$code;
    echo json_encode($response); 
 }

AJAX

$("#register").validate({
    rules: {
        name:{required:true,minlength:3},
        country:{required:true},
        mobileno:{required:true,minlength:10,maxlength:10,number: true},
        email:{required:true,email: true}
    },

    submitHandler: function (r) {
        $.ajax({
            url: base_url + "/process.php",
            type: "post",
            dataType: 'json',
            data: $('#register').serialize(),
            success: function (response) {
                if (response.error_no == '1') {
                    $('#name').html(response.error);
                } else if (response.error_no == '2') {
                    $('#country').html(response.error);
                } else if (response.error_no == '3') {
                    $('#mobileno').html(response.error);
                } else if (response.error_no == '4') {
                    $('#email').html(response.error);
                } else if (response.error_no == '6') {
                    $('#failed').html(response.error);
                }else{
                    window.location.href=base_url+"/thankyou.php";
                }
            }
        })
      }
    });

Database connection

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
//echo "Connected successfully";
?>

Your script is open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) You should consider using [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's instead of concatenated values — RiggsFolly, Jan 07 '20 at 12:01
`global $currentdate;` Why does that sound like such a bad thing to do, someone help me out here — RiggsFolly, Jan 07 '20 at 12:01
@RiggsFolly, I added the timezone code before the function. So I added global $currentdate; — Naren Verma, Jan 07 '20 at 12:03
@RiggsFolly, Your script is open to SQL Injection Attack. I need to know where I am wrong in this with my code? I checked the 6 revs, 4 users 78% Danijel answer — Naren Verma, Jan 07 '20 at 12:05
Thats why that comment has 4 different links in it, so you can go off and have a good read — RiggsFolly, Jan 07 '20 at 12:06
If you are using a prepare'd statement, you do not need to escape the data. Thats one of the prime functions of preparing a query. The prepare sends the query to the database and compiles and optimizes it. So when you bind data before an expecute, that data cannot effect the execution of the query like it could if you were concatenating values into a string and then executing the string — RiggsFolly, Jan 07 '20 at 12:10
@RiggsFolly, Ok, I got your last comment. I removed $conn->real_escape_string — Naren Verma, Jan 07 '20 at 12:15

Baracuda078 · Answer 1 · 2020-01-07T12:19:47.507

0

As far I know you don't need real_escape_string with prepared statements.

For the POST varables you maybe better can use isset, like:

$name = isset($_POST['name']) ? $conn->real_escape_string(trim($_POST['name'])) : '';

To prevent undefined errors

$country needs to be an integer when i take a look at your query.

$country= isset($_POST['country']) ? (int)$_POST['country']) : 0;

And then in the elseif check if $country > 0

For email check you can also use:

if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
// No valid email
}

And maybe after excecute check for affected rows te be sure the data is really inserted.

A sanitize function can be necessary for showing the database results in your html

edited Jan 07 '20 at 12:19

answered Jan 07 '20 at 12:10

Baracuda078

677
1
5
10

1

_As far I know you don't need real_escape_string with prepared statements._ So why does your next line escape the inputs – RiggsFolly Jan 07 '20 at 12:12
I copied his code, for the insert or update its not necessary – Baracuda078 Jan 07 '20 at 12:14

Post data security in PHP using prepared statement

1 Answers1