How to Insert Special Characters Into a Database in PHP

Sarwan Soomro Feb 02, 2024
  1. Create HTML Form and Set Database Connection to Collect Data
  2. Use mysqli_real_escape_string() to Insert Special Characters Into a Database in 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.

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>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</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>&nbsp;</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:

PHP: Insert Data With mysqli_real_escape_string

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:

PHP: Insert Data without mysqli_real_escape_string

As we can see, it failed to execute and collect the data because of the special characters in the user’s input.

Sarwan Soomro avatar Sarwan Soomro avatar

Sarwan Soomro is a freelance software engineer and an expert technical writer who loves writing and coding. He has 5 years of web development and 3 years of professional writing experience, and an MSs in computer science. In addition, he has numerous professional qualifications in the cloud, database, desktop, and online technologies. And has developed multi-technology programming guides for beginners and published many tech articles.

LinkedIn

Related Article - PHP MySQL