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
2 Answers
Reset to default 4I 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.