MySQL flashing via HTML form using PHP is happening, but the data is not searchable
I have created a MySQL database that currently has only one table named "users". Fields: id, first_name, last_name, username, email, about, location, website. I am inserting data via an auto-submitted HTML form from PHP.
Inserts happen without issue, but what this turns me off is that when the insertion is done through an HTML form, the data inside is not searchable. For example, if I try to do a search query to find one user with a matching email address or username, the user will not be found, even if it exists in the database. It is only when searching for a user with his id (which is auto-incrementing and auto-inserted by MYSQL) that the search query finds from the user. Below is my code. I have reversed everything from CSS to validation and security features to rule out factors that could cause 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 data is inserted into the database directly via an MSQL query script, avoiding the HTML table and the $ _POST super-globals like ...
$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 data inside all fields can be used to find and match any existing user: email, username, first_name, etc., not just the "ID" field as I mentioned earlier when this happens when inserting through an HTML form.
I am using WAMP server 2.4, MySQL version is 5.6.12 and PHP version is 5.4.12
Hope I understood my description of the problem, and mostly hope you can help me figure out why this is happening.
Thank you very much in advance!
Arturo.
source to share
Taking some wild guesses, but ...
Here's your problem
value=" "
This sets your input fields with a single space character. When you click on these fields, you probably won't notice the whitespace before or after your cursor. I would say there are good chances that all of your field values end in leading or trailing space.
The first thing I would do is set the attributes value
to empty i.e.
<input type="text" name="first_name" value="">
You can also trim()
values in your PHP code ...
$first_name = trim($_POST["first_name"]);
and finally your statement INSERT
(and possibly all your other queries) is vulnerable to SQL injection. I highly recommend using prepared statements like
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
source to share