sql - Why line returns are not reapply after doing esc_sql?

admin管理员组

文章数量:1278854

I trying show data from my database. This data was escape by esc_sql like bellow.

But when I use it in echo, line-returns are print as normal chars ('\', 'r', '\', 'n').

$data = esc_sql("
    sample data with line returns
");

echo $data; // Output = \r\n    sample data with line returns\r\n

What is the best way to re-apply line returns after doing esc_sql ?

I trying show data from my database. This data was escape by esc_sql like bellow.

But when I use it in echo, line-returns are print as normal chars ('\', 'r', '\', 'n').

$data = esc_sql("
    sample data with line returns
");

echo $data; // Output = \r\n    sample data with line returns\r\n

What is the best way to re-apply line returns after doing esc_sql ?

• sql

Share Improve this question edited Nov 8, 2021 at 15:42 Adrien Villalonga asked Nov 8, 2021 at 15:36 Adrien VillalongaAdrien Villalonga 18388 bronze badges 10

• esc_sql is for values you're passing in to the database, not values taken from the database to display. You should escape them for HTML instead. – Rup Commented Nov 8, 2021 at 15:44
• I use esc_sql only before insert but when I access it by a wpdb->get_results the result is same – Adrien Villalonga Commented Nov 8, 2021 at 15:47
• Sorry I misspoke: esc_sql is for values you're concatenating into SQL statements. Which you shouldn't be doing unless you absolutely have to. It's not for values you're passing into the database any other way e.g. parameterised statements (which is how you should be doing it). You probably don't need to esc_sql the values. – Rup Commented Nov 8, 2021 at 15:49
• As i can see here (esc_sql() | Function) on BasicExample values are escape too – Adrien Villalonga Commented Nov 8, 2021 at 15:55
• Yes, but they're being assembled into the SQL SELECT statement string. You're not doing that to insert data are you? – Rup Commented Nov 8, 2021 at 15:56

 |  Show 5 more comments

1 Answer 1

Sorted by: Reset to default 2

That's not what esc_sql is for, or what it should be used for.

Safely Escaping Variables In SQL Queries

To make variables safe for an SQL query, use $wpdb->prepare, e.g.

$table_name = "{$wpdb->prefix}myTable";
$myID = 12;
 
$wpdb->query(
    $wpdb->prepare(
        "UPDATE `$table_name` SET `your_column_1` = 1 WHERE `$table_name`.`your_column_id` = %d",
        $myID
    )
);

Notice that $myID is safely inserted into the SQL query using $wpdb->prepare, and is not directly included in the query string. esc_sql is not used here.

Safely Escaping Query Results

To escape data you have retrieved from the database on output, use the function that matches the context it's being displayed in

• esc_html for non-HTML text
• esc_attr for HTML tag attributes
• esc_url for URLs
• wp_kses_post for content with tags allowed in post content

When Should I Use esc_sql?

Very, very, rarely:

Usually you should prepare queries using wpdb::prepare(). Sometimes, spot-escaping is required or useful. One example is preparing an array for use in an IN clause.

and

Be careful in using this function correctly. It will only escape values to be used in strings in the query. That is, it only provides escaping for values that will be within quotes in the SQL (as in field = '{$escaped_value}'). If your value is not going to be within quotes, your code will still be vulnerable to SQL injection. For example, this is vulnerable, because the escaped value is not surrounded by quotes in the SQL query: ORDER BY {$escaped_value}. As such, this function does not escape unquoted numeric values, field names, or SQL keywords.

https://developer.wordpress/reference/functions/esc_sql/

Most WP developers will never use or encounter esc_sql and that's a good thing.

本文标签： sqlWhy line returns are not reapply after doing escsql