Blog

It is usually a case to enter special characters in SQL right?, but don’t worry! I have prepared this special article about introducing rare characters in SQL, where I explain in detail how to escape every possible character that comes our way on a day-to-day basis.

It is very common to forget escape special characters when we enter information into the database whatever the source. If we talk about backend programming with PHP, for example, this happens if we don’t remember to use specific functions for it before performing an INSERT SQL, UPDATE SQL or SELECT SQL or even a CONCAT SQL.

Quotes, both single and double, are usually the most common error problem, closely followed by backslashes when, for example, we talk about entering file paths in the database.

Next I will solve the following cases:

1. Escape special characters in SQL queries.
   1. Escape single quotes.
   2. Escape double quotes.
   3. Escape counterslashes.
2. Escape characters for SQL with php.
   1. Enter special characters with mysqli.
   2. Escape special characters with the PDO class.

Content

• Escape special characters in SQL
  • Escape single quote in SQL
  • Escape Double Quotes in SQL
  • Escape Backslash in SQL
• Escape characters for SQL with php
  • Fix special characters by mysqli procedures
  • Escape special characters with mysqli class
  • Enter special characters with the PDO class
  • Enter special characters with PDO form 2

Escape special characters in SQL

As I have previously mentioned, the special characters that usually cause problems when inserting them are:

• Single quotes
• double quotes
• Contraslash or Backslash \

Escape single quote in SQL

The typical problem could be something like this:

This quote inserted between the L and the O would produce an outrageous error when executing the query in MySQL or any other DBMS. To manually escape the single quote, that is, writing a character just before the conflict symbol, can be done in 3 ways:

1. Adding a single quote just before the quote we type, that is, doubling each single quote.
2. Adding a backslash in front of the quotation mark.
3. Changing the delimiters of the query, that is, we can write the variables of type text between double quotes.

The three possible solutions would be:

1. UPDATE city SET name = ‘L”Origuilla’ WHERE name LIKE ‘Origuilla’;
2. UPDATE city SET name = ‘L\’Origuilla’ WHERE name LIKE ‘Origuilla’;
3. UPDATE city SET name = “L’Origuilla” WHERE name LIKE ‘Origuilla’;

Escape Double Quotes in SQL

Although the custom of programming SQL is to write the texts in quotes, it is common that if you program in other languages ​​you end up delimiting the texts in double quotes, this will possibly result in the following problem:

This double quote inserted between the delimiters of the same symbol would cause an error. To avoid this problem we can manually escaping double quotes once again in 3 ways:

1. Adding a double quote just before the quote we write, that is, doubling each double quote.
2. Adding a backslash in front of the double quotes.
3. Changing the delimiters of the query, that is, we can write the variables of type text between single quotes.

The three possible solutions would be:

• UPDATE comment SET text = “I’m not referring to your “”codes”” ” WHERE id = 33;
• UPDATE comment SET text = “I don’t mean your \”codes\” ” WHERE id = 33;
• UPDATE comment SET text = ‘I don’t mean your “codes” ‘ WHERE id = 33;

Escape Backslash in SQL

It would have to be changed with double backslash to be correct:

Escape characters for SQL with php

If you program in php you have several strategies to solve the problem. We must always have made the connection before using any of the following options:

• Use the function mysqli_real_escape_string($link, $escaping_string).
• For connections through the class mysqli make use of the real_escape_string($escape_string) method.
• Use the prepare() and bindValue() or quote() method of the PDO class (if you make the connection through this object).

Let’s see them one by one with examples of use.

Fix special characters by mysqli procedures

For the connection to MySQL through procedures we have the mysqli_real_escape_string() function. We pass a string to this function, and it returns a result string with the escaped characters depending on the range of characters that the connection accepts. If you created your database with, for example, configuration of english characters us will escape symbols like ñas well as single and double quotes or backslash (among others).

It must be remembered that this function depends entirely on a connection to the database through mysqli procedures:

<?php
$conexion = mysqli_connect('localhost', 'root', 'root');
mysqli_select_db('test', $conexion); //selecciona la base de datos
$ciudad = "L'Origuilla";
$ciudad_escapada = mysqli_real_escape_string($conexion, $ciudad);
$query = "UPDATE ciudad SET nombre = '$ciudad_escapada' WHERE nombre LIKE 'Origuilla';
$resultado = mysqli_query($conexion, $query);
if($resultado == false){
    echo 'ERROR! CODIGO: ' . mysqli_errno($conexion) . ' mensaje:'  . mysqli_error($conexion);
}else{
    echo 'Ciudad actualizada!';
}
?>

mysqli_real_escape_string() escapes special characters from a string to use it in an SQL statement, taking into account the current character set of the connection.

Escape special characters with mysqli class

If you make your database connections with the mysqli class, you will be able to escape the problematic characters using the real_escape_string() method. This method requires an active connection to MySQL in order to do its job, otherwise it will return an empty string.

<?php
$objeto_mysqli = new mysqli('localhost', 'root', 'root', 'test');
$ciudad = "L'Origuilla";
$ciudad_escapada = $mysqli->real_escape_string($ciudad);
$query = "UPDATE ciudad SET nombre = '$ciudad_escapada' WHERE nombre LIKE 'Origuilla';
$resultado = $objeto_mysqli->query($query);
if($resultado != false){
   echo 'La ciudad se ha actualizado correctamente';
}
?>

The method real_escape_string() of the class mysqli escapes special characters from a string taking into account the set of characters set in the database of the connection with MySQL.

Enter special characters with the PDO class

Class PDO allows, without the extra use of methods, to escape the special characters of our queries. Just by preparing our queries before executing them, the class PDO will escape quotes, backslashes, etc. Let’s see what I mean with an example:

try{
     $objetoPDO = new PDO('mysql:host=localhost;dbname=Test, 'root', 'root');
}catch (PDOException $e) {
      echo "¡Error!: " . $e->getMessage();
      die();
}
$query = "SELECT * FROM usuario WHERE nickname LIKE :nombre;";
$pdoStat = $objetoPDO->prepare($query);
$pdoStat->bindValue(':nombre', "l'asdas'", PDO::PARAM_STR);

Every time I substitute a variable from PDO by a value, the method bindValue() escape conflicting characters and inserts the resulting string in its corresponding place in the query, thus building a correct query that understands the character set of the database to which it is connected with PDO.

Enter special characters with PDO form 2

There is a second way to enter special characters using PDO connections, using the quote() method. This method, normally unnecessary if we make our queries by using bindValue either bindParam after a prepare() of the query, it allows us to escape the conflicting characters (single, double quotes, backslash, etc) and get the “translated” text strings. As always in Sr source code, let’s see it with a code example:

Let’s imagine that I am going to search for users by the name entered in a method=”GET” form:

<?php
$objetoPDO = new PDO('mysql:host=localhost;dbname=Test, 'root', 'root');
$nombre = $objetoPDO->quote($_GET['nombre']);
$query = "SELECT * FROM usuario WHERE nickname LIKE $nombre";
$pdoStat = $objetoPDO->prepare($query);
$result = $pdoStat->execute();
?>

In this example, in a different way than the previous one in which I used prepare() and bindValue(), but with the same result, I have escaped the special characters that may contain the name entered into the form, and I have mounted the query, obtaining a smooth execution of the query and also avoiding SQL Injection.