HOWTO · PHP
How to Insert Special Characters Into a Database in PHP
This tutorial will introduce the mysqli_real_escape_string() function to insert special characters into a database in PHP. We will compare inserting data with and without the mysqli_real_escape_string() function.
On this page
This tutorial will introduce the mysqli_real_escape_string() function to insert special characters into a database in PHP. We will compare inserting data with and without the mysqli_real_escape_string() function.
Create HTML Form and Set Database Connection to Collect Data
First, an HTML form must be created for users to input data, and a database connection must be set for sending commands and receiving input.
The following are the codes we will use throughout this tutorial.
HTML code:
<!DOCTYPE html>
<head>
<title>
Real Escape Strings
</title>
</head>
<body>
<form action = 'login.php' method = 'post' >
<div align="ccenter">
<label for="username"><b>User Name</b> </label>
<input type="text" placeholder="Your Name" name="username" required>
</br>
<label for="password"><b>User Password</b></label>
<input type="password" placeholder="Your Password" name="userpassword" required>
</br>
<label for="userlocation"><b>User Location</b> </label>
<input type="text" placeholder="Your Location" name="userlocation" required>
</br> </br> </br>
<input type="submit" name="1" value = "Insert without mysqli_real_escape_string " />
</br>
</br>
<input type="submit" name="2" value = "Insert with mysqli_real_escape_string " />
</div>
</form>
</body>
</html>
Database connection code:
<?php
error_reporting(0);
define("server", "localhost");
define("user", "root");
define("password", "");
define("database", "demo");
$mysqli = mysqli_connect(server , user, password, database);
if ($mysqli -> connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}
//db connection code ends
?>
We obtain the following fundamentals:
- Form Input Fields
- Database Name:
demo - Table Name:
user_login
Use mysqli_real_escape_string() to Insert Special Characters Into a Database in PHP
To get user input with special characters from the form fields, we use the mysqli_real_escape_string() function.
We need the following parameters: database connection and the strings we want to escape. Our database connection is $mysqli, and the string we want to escape is $_POST['username'].
Procedural style syntax:
<?php
//Note: This is a demo syntax, you can run the next PHP code blocks for output
$username= $mysqli_real_escape_string($mysqli, $_POST['username']);
?>
Example:
<?php
//Second example: insert user data using mysqli real escape string
//ensure that users do not send empty data, see the following condition
if(isset($_POST['2']) && !empty($_POST['username']) && !empty ($_POST['userpassword']) && !empty ($_POST['userlocation']))
{
//You apply real_escape_string on $_POST[''] form fields (user input)
$username = mysqli_real_escape_string($mysqli, $_POST['username']);
$userpassword = mysqli_real_escape_string($mysqli, $_POST['userpassword']);
$userlocation = mysqli_real_escape_string($mysqli, $_POST['userlocation']);
//same query (the values are dynamic, we escaped the strings)
$query="INSERT INTO user_login (username, userpassword, userlocation) VALUES ('$username', '$userpassword', '$userlocation')";
if (!mysqli_query($mysqli, $query)) {
echo "Failed!";
}
else{
//success
echo "Data inserted";
}
//close connection
$mysqli -> close();
}// ends first condition
Output:
The mysqli_real_escape_string() function eliminates special characters, consequently increasing the security of the database.
It generates a valid SQL expression for use in SQL queries. In addition, it is also a relatively secure method to avoid SQL injection besides prepared statements.
Consider another example below, which does not use the mysqli_real_escape_string() function.
<?php
//example-1: insert data without mysql real escape
if(isset($_POST['1'])){
$username = $_POST['username'];
$userpassword = $_POST['userpassword'];
$userlocation= $_POST['userlocation'];
$query="INSERT INTO user_login (username, userpassword, userlocation) VALUES
('$username', '$userpassword', '$userlocation')";
if (!$mysqli -> query($query)){
echo "Query failed to execute because of special characters!";
}
else{
echo "User Data Inserted!";
}
//first example ends here
}
?>
Output:
As we can see, it failed to execute and collect the data because of the special characters in the user’s input.
