While creating an application in SQL, it is easier to get attacked by SQL injection. SQL injection is one of the most frequent web risks that can attack a website when the query is not secure. This will allow the attacker to manipulate the SQL query or access the data. The mysql_real_escape_string() is a PHP function that is used to escape special characters in SQL queries. It will not give full security against SQL injection. In this blog, you will learn different methods to protect the application effectively.
Table of Contents:
What is mysql_real_escape_string()?
The mysql_real_escape_string() is a PHP function used to escape special characters that are used in SQL queries. It has special characters like quotes (‘), backslashes (/), and null (/0) that can help to prevent SQL injection.
Limitations of mysql_real_escape_string()
- It is not efficient when it comes to numeric or special input vulnerabilities. It only helps to escape string inputs.
- It cannot protect against logic manipulation.
- It can’t prevent hacking tricks that change the data format.
- The mysql_real_escape_string() will not work on the new version of PHP.
Methods to Bypass mysql_real_escape_string()
There are many methods to bypass SQL injections, like binary injection, truncate input attack, etc. Before avoiding it, we will set up the MySQL server.
1. Install MySQL on your Windows or Mac.
2. Set up the environment.
3. Select the database:
SELECT DATABASE();
If there is no database, create an SQL injection database.
4. Create an SQL injection database.
CREATE DATABASE sql_injection_test;
5. Start using the SQL injection database
USE sql_injection_test;
Now, let’s create a user table and insert the values.
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
password VARCHAR(255) NOT NULL
----Insert the values
INSERT INTO users (username, password) VALUES
('admin', 'admin123'),
('user1', 'password1'),
('user2', 'password2');
----Verify the data
SELECT * FROM users;
Output:
These are the values inserted into the user table and the SQL injection database.
Method 1: Binary String Injection in MySQL Server
MySQL uses utf8mb4 to prevent the attacker from escaping with binary strings. The injection will occur when the application is not handled properly. The COLLATE utf8mb4 is used to prevent character corruption.
Set the character set to utf8mb4 before querying.
Example:
SHOW CREATE TABLE users;
SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;
After setting up, write the query to prevent injection.
SELECT * FROM users WHERE username = 'admin' AND password = 'pass' COLLATE utf8mb4_general_ci;
Output:
Explanation: This will work well if the password column uses the BLOB type. By default, mysql_real_escape_string() will not escape binary input. This will compromise system security if it is not authenticated properly.
The truncated input attack will occur when the application forces input length restrictions, but the database grants permission to longer input values. This may lead to a compromised network.
Example:
Assume the database stores passwords in 10 characters, but the database can accept longer inputs:
INSERT INTO users (username, password) VALUES ('admin', 'admin12345');
SELECT * FROM users WHERE username = 'admin' AND password = 'admin12345';
If the application truncates the password from “admin12345” to “admin123” but mysql_real_escape_string() was used before truncation, the application becomes vulnerable, and the escape string is lost.
With this query, the attacker can bypass the authentication:
SELECT * FROM users WHERE username = 'admin' ;
Output:
Explanation: Now, the rest of the query is neglected, and the permission is granted to the attacker.
Method 3: Encoding Tricks in MySQL
The encoding tricks in MySQL are like when some web applications decode the user input before executing the query. This will allow the attacker to bypass security using the double encoding method.
Example:
SELECT * FROM users WHERE username = 'admin%27 OR %271%27=%271';
%27 This URL is for encoding the single quotes.
Then, it decodes the single quotes (‘) back, and the query becomes.
SELECT * FROM users WHERE username = 'admin' OR '1'='1';
Output:
Explanation: The web application got injected with queries by the double encoding method. Now the attacker can have access to the data. If the application automatically decodes input before transferring it to the database, it will, by default, create a malicious code that will help the attacker.
Method 4: Using Escaped Strings in Numeric Context in MySQL
If the mysql_real_escape_string() is used on numeric inputs in the web application database, it will treat them as strings because MySQL will automatically convert the numbers to strings.
To bypass this, we can use,
Example:
SELECT * FROM users WHERE id = '1' OR 1=1;
Output:
Explanation: Here, as 1=1 is true, the application granted permission to the attacker by bypassing the security.
To fix this issue, always use PREPARED STATEMENTS:
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$id]);
Real-World Examples
Case 1: bypassing the login ID authentication.
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL,
password TEXT NOT NULL
);
INSERT INTO users (username, password) VALUES
('admin', 'admin123'),
('user1', 'password1'),
('user2', 'password2');
SELECT * FROM users WHERE username = 'admin' AND (password = '' OR '1'='1');
Output:
Explanation: If the application does not use prepared statements to protect itself. Then the attacker will get into the application without proper credentials.
Case 2: To get database information:
CREATE TABLE database_info (
id INT AUTO_INCREMENT PRIMARY KEY,
schema_name VARCHAR(255) NOT NULL
);
INSERT INTO database_info (schema_name) VALUES
('test_db'),
('prod_db'),
('backup_db');
SELECT database();
SELECT schema_name FROM information_schema.schemata;
Output:
Explanation: Here, the query FROM information_schema.schemata; fetches the details of the database.
Case 3: To get all the user’s credentials
CREATE TABLE users_credentials (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL
);
INSERT INTO users_credentials (username, password) VALUES
('admin', 'hashed_admin_password'),
('user1', 'hashed_user1_password'),
('user2', 'hashed_user2_password');
After creating the table, switch the database.
USE sql_injection_test;
SELECT username, password FROM users;
Output:
Explanation: Here, the query executed successfully and exposed all the user’s stored credentials in a database.
Case 4: To delete a database
If the user’s database is not properly authenticated, the attacker can gain access and delete all the databases from the system.
Example:
DROP TABLE users;
Output:
Explanation: Here, the drop command deleted all the databases where the user failed to protect the access control.
How to Prevent SQL Injection
- To prevent SQL injection, you can simply use prepared statements when creating an application.
- The prepared statement stores the numeric values as integers and input types.
- It will filter the input and validate it before processing.
- It has a web application firewall (WAF) to deploy and detect SQL injection attempts.
Steps to run the PHP prepared statement in XAMPP and MySQL.
Step 1. Go to this file location after installing XAMPP: C:\xampp\htdocs\myproject\ and create a new folder as myproject.
Step 2. Save this query in Notepad as a query.php file in your system. To that file location.
Step 3. Run the script in the browser.
Step 4. Type this in the address bar:
http://localhost/myproject/index.html
Step 5. Enter the username and password, and then the login is completed successfully. This will send the data to login.php.
<?php
// Database connection using PDO
$host = "localhost";
$dbname = "your_database";
$username = "your_username";
$password = "your_password";
try {
// Create a new PDO instance
$pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//Simulating user input from a login form
$inputUsername = $_POST['username'] ?? '';
$inputPassword = $_POST['password'] ?? '';
//Secure Prepared Statement Query**
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username");
$stmt->execute(['username' => $inputUsername]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);
if ($user && password_verify($inputPassword, $user['password'])) {
echo "Login successful!";
} else {
echo "Invalid credentials.";
}
Conclusion
By now, you can easily avoid SQL injection with mysql_real_escape_string() using various methods like binary injection, encoding, and truncating. To prevent SQL injection, you can use prepared statements that validate and implement security for your web application using a Web Application Firewall (WAF). These steps will help you secure your web application. In this blog, you have gained knowledge on how to prevent SQL injection and how attackers will attack.
Take your skills to the next level by enrolling in the SQL Training Course today and gain hands-on experience. Also, prepare for job interviews with our SQL interview questions, prepared by industry experts.
SQL Injection That Gets Around mysql_real_escape_string() – FAQs
1. Why is mysql_real_escape_string() not enough to prevent SQL injection?
It only escapes special characters in strings but does not protect against logic manipulation or numeric input attacks.
2. How can attackers bypass mysql_real_escape_string()?
Using techniques like binary injection, truncated input attacks, encoding tricks, and numeric context manipulation.
3. What is the safest way to prevent SQL injection?
Using prepared statements with parameterized queries instead of relying on escaping functions.
4. Can SQL injection delete entire databases?
Yes, if proper security measures aren’t in place, an attacker can execute DROP TABLE or other destructive commands.
5. How can web applications detect SQL injection attempts?
By implementing Web Application Firewalls (WAFs) and monitoring logs for suspicious queries.