The SQL Injection in MySQL is a harmful approach where an attacker inserts or “injects” harmful SQL code into a database query. This can be done through user inputs such as forms, URL parameters, or cookies. The attacker takes advantage of weaknesses in the software to steal information from the database.

How SQL Injection Works

Imagine you have a web application with a login page. When a user enters their username and password, the application checks these credentials against a MySQL database. The SQL query might look like as given below −

SELECT * FROM users 
WHERE username = ''user'' AND password = ''password'';

In a secure application, the ”user” and ”password” would be the actual values entered by the user. However, in an SQL Injection attack, an attacker can manipulate the input fields to inject malicious SQL code.

For example, they might enter the following as the username −

'' OR ''1'' = ''1

Now, the SQL query becomes −

SELECT * FROM users 
WHERE username = '''' OR ''1'' = ''1'' AND password = ''password'';

Because ”1” always equals ”1”, this condition is always true, and the attacker gains unauthorized access to the application. In this way, they trick the application into granting access without a valid password.

Preventing SQL Injection

To prevent SQL injection, it is important to handle escape characters properly when using scripting languages like PERL and PHP. When working with PHP and MySQL, you can use the mysql_real_escape_string() function to escape input characters that have special meaning in MySQL. Following is an example of how to do this −

if (get_magic_quotes_gpc()) {
   $name = stripslashes($name);
}
// escape input characters
$name = mysql_real_escape_string($name);

// Perform the MySQL query with the escaped ''name''
mysqli_query("SELECT * FROM CUSTOMERS WHERE name=''{$name}''");

The LIKE Quandary

Now, let us address the issue with the LIKE clause. When dealing with user-provided data that may include ”%” and ”_” characters, it is important to create a custom escaping mechanism to treat them as literals. You can achieve this by combining “mysql_real_escape_string()” function with “addcslashes()” function, which allows you to specify a character range to escape. Following is an example of how you can do it −

// Escape and convert ''%'' and ''_'' in the user-provided string
$sub = addcslashes(mysql_real_escape_string("%str"), "%_");

// $sub will be equal to %str_

// Use the escaped string in the LIKE query
mysqli_query("SELECT * FROM messages 
   WHERE subject LIKE ''{$sub}%''");

In this way, you ensure that the ”%” and ”_” characters in the user input are treated as literal characters in the SQL query, preventing SQL injection and maintaining the integrity of your database operations.