MySQL insertion through an HTML form using PHP is happening but the data is not searchable

Question

I have created a MySQL database that currently has just one table called "users". Fields are: id, first_name, last_name, username, email, about, location, website. I am inserting the data through an auto submitted HTML form with PHP.

The insertions are happening with no problem, but what it is tripping me up, is that when the insertion is made through the HTML form, the data inside is not searchable. For example if I try to perform a search query to find one user with a matching email or username, the user is not found even though that it does exists in the database. It's only when I search for a user with his ID (which is an auto-increment and inserted automatically by MYSQL) that the search query finds the user. Below is my code. I have striped everything from CSS to verification and security functions, in order to rule out factors that might be causing this.

<?php
if (isset($_POST['submit'])) {


    //$user = new User();
    $first_name =$_POST["first_name"];
    $last_name =$_POST["last_name"];
    $email =$_POST["email"];
    $username =$_POST["username"];

    $connection=mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_NAME);

    $sql = "INSERT INTO users ";
    $sql .= "(first_name, last_name, email, username) ";
    $sql .= "VALUES ('{$first_name}', '{$last_name}', '{$email}', '{$username}')";
    $result = mysqli_query($connection, $sql);
    if ($result) {
        echo "Insertion succeed";
    } else {
        echo "Insertion failed";
    }
}
?>


<h2>Sign up</h2>

<form  action="sign_up2.php" method="post"/>
<ul>
<li>
First_name: <input type="text" name="first_name" value=" "/>
</li>
<li>
Last_name:<input type="text" name="last_name" value=" "/>
</li>
<li>
Email:<input type="text" name="email" value=" "/>
</li>
<li>
Username:<input type="text" name="username" value=" "/>
</li>
<li>
Password:<input type="password" name="password" value=""/>
</li>

<li>
<input type="submit" name="submit" value="Sign in" />
</li>
</ul>
</form>

On the other hand, if the data is inserted to the database straight through the MSQL query script avoiding the HTML table and $_POST super globals, like this...

$connection=mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_NAME);

    $sql = "INSERT INTO users ";
    $sql .= "(first_name, last_name, email, username) ";
    $sql .= "VALUES ('John', 'Doe', 'John@gmail.com', 'john_d')";
    $result = mysqli_query($connection, $sql);
    if ($result) {
        echo "Insertion succeed";
    } else {
        echo "Insertion failed";
    }

....all of the data inside all the fields can be used to find and match any existing user: email, username, first_name, etc, not just exclusively with the ‘ID’ field as I mentioned before that happen when the insertion is made through the HTML form.

I am using WAMP server 2.4, MySQL version is 5.6.12 and PHP version is 5.4.12

I hope I was clear with my description of the problem and mostly I hope that you can help me to figure out why is this happening.

Many thanks in advance!

Arturo.

http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php/60496#60496 — user2864740, Oct 23 '14 at 23:12

Phil · Answer 1 · 2014-10-23T23:36:18.740

Taking some wild guesses but...

Here's your problem

value=" "

That sets up your input fields with a single space character. When you click into those fields, you probably don't notice the space character either before or after your cursor. I'd say there's a good chance all your field values end up with a leading or trailing space.

First thing I'd do is set the value attributes to empty, ie

<input type="text" name="first_name" value="">

You can also trim() the values in your PHP code...

$first_name = trim($_POST["first_name"]);

and finally, your INSERT statement (and probably all of your other queries) are vulnerable to SQL injection. I highly recommend using prepared statements, eg

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$connection = new mysqli(DB_SERVER, DB_USER, DB_PASS, DB_NAME);

$stmt = $connection->prepare('
    INSERT INTO users (first_name, last_name, email, username)
    VALUES (?, ?, ?, ?)
');
$stmt->bind_param('ssss', $first_name, $last_name, $email, $username);
$stmt->execute();

echo "Insertion succeed";
// any problems will trigger an exception so handle that however you want

Wow Phil, Thank you very much. You were spot on. That did in fact took care of the problem. You are a star! I had functions that were escaping the values prior to the insertions to avoid mysql injection. Took them out to rule out any other factor that might be causing the problem. I was doing it though striping slashes, but I will try the way you are suggesting. Thank you so much Phil!! — Arturo Rosales Baez, Oct 24 '14 at 15:43

MySQL insertion through an HTML form using PHP is happening but the data is not searchable

1 Answers1