Using WordPress ‘esc_sql()’ PHP function

by

The esc_sql() WordPress PHP function is used to escape data for use in a MySQL query. This is often necessary when you need to prepare an array for use in an IN clause, for instance. Since version 4.8.3, it replaces ‘%’ characters with a placeholder string to prevent certain SQL injection attacks. However, it only provides escaping for values that will be within quotes in the SQL. It doesn’t escape unquoted numeric values, field names, or SQL keywords.

Usage

$name = esc_sql( $name );
$status = esc_sql( $status );
$wpdb->get_var( "SELECT something FROM table WHERE foo = '$name' and status = '$status'" );

In the example above, we’re escaping the $name and $status variables before using them in a SQL query.

Parameters

  • $data (string|array) – This is the unescaped data that you want to escape.

More information

See WordPress Developer Resources: esc_sql()

This function has been implemented since the early versions of WordPress and is not deprecated. However, be aware of its limitations and use wpdb::prepare() whenever possible.

Examples

Escaping a Single String

$name = "O'Reilly";
$escaped_name = esc_sql( $name ); // Escapes the name
// Now $escaped_name can be safely used in a query

In this example, we escape a single string that contains a character that would otherwise break a SQL query.

Escaping an Array of Strings

$names = array("O'Reilly", "D'Artagnan", "Mc'Donald");
$escaped_names = array_map('esc_sql', $names); // Escapes each name in the array
// Now $escaped_names can be safely used in a query

Here, we’re escaping an array of strings. Each name in the array is passed through the esc_sql() function using array_map().

Using Escaped Strings in a Query

$name = "O'Reilly";
$escaped_name = esc_sql( $name ); // Escapes the name
$wpdb->get_var("SELECT id FROM users WHERE last_name = '$escaped_name'"); // Uses the escaped name in a query

This example demonstrates how to use an escaped string in a query.

Escaping User Input for a Search Query

$search_term = $_GET['search']; // User input from a form, for example
$escaped_search_term = esc_sql( $search_term ); // Escapes the search term
$wpdb->get_results("SELECT * FROM posts WHERE post_title LIKE '%$escaped_search_term%'"); // Uses the escaped search term in a LIKE clause

In this case, we escape user input before using it in a search query.

Escaping a Complex String

$complex_name = "O'Reilly & Sons, %Co.";
$escaped_complex_name = esc_sql( $complex_name ); // Escapes the complex name
// Now $escaped_complex_name can be safely used in a query

This last example shows how the function handles a complex string with multiple characters that require escaping.