最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

escaping - How do I escape a table name or column name in SQL? esc_sql doesn't do this

programmeradmin3浏览0评论

If you want to escape string values in an SQL query, you can use WordPress's esc_sql function:

<?php

$wpdb->get_var( "SELECT * FROM something WHERE foo = '" . esc_sql( $foo ) . "'" );

You can also use the much more convenient prepare function like this:

<?php

$wpdb>-get_var(
    $wpdb->prepare(
        "SELECT * FROM something WHERE foo = %s",
        $foo
    )
);

However, esc_sql is not suitable for escaping table names or column names, (only string values). And there is no way to use prepare for escaping table names or column names.

How can I escape $foo and $bar properly in this example SQL query?

SELECT * FROM $foo WHERE $bar = "example";

If you want to escape string values in an SQL query, you can use WordPress's esc_sql function:

<?php

$wpdb->get_var( "SELECT * FROM something WHERE foo = '" . esc_sql( $foo ) . "'" );

You can also use the much more convenient prepare function like this:

<?php

$wpdb>-get_var(
    $wpdb->prepare(
        "SELECT * FROM something WHERE foo = %s",
        $foo
    )
);

However, esc_sql is not suitable for escaping table names or column names, (only string values). And there is no way to use prepare for escaping table names or column names.

How can I escape $foo and $bar properly in this example SQL query?

SELECT * FROM $foo WHERE $bar = "example";
Share Improve this question asked Sep 20, 2017 at 13:49 FlimmFlimm 7207 silver badges25 bronze badges
Add a comment  | 

2 Answers 2

Reset to default 4

I can't find a function shipped with WordPress that does this, so I created my own:

 function esc_sql_name( $name ) {
     return str_replace( "`", "``", $name );
 }

You can use it like this:

 $escaped_name = esc_sql_name( $column_name );

 $sql = $wpdb->prepare(
       "SELECT * FROM example WHERE `$escaped_name` = %s",
       $foobar
 );

Reference:

  • MySQL documentation on identifiers

Use the function sanitize_key

See the developer docs here: https://developer.wordpress/reference/functions/sanitize_key/

Note: it does perform a strtolower as WordPress Core Standards do require lowercase column names.

发布评论

评论列表(0)

  1. 暂无评论