mysql - wpdb->prepare function remove single quote for %s in SQL statment

admin管理员组

文章数量:1278948

I am using the below wpdb->prepare function in a sql statement. I am passing in several variables some field names and some values. When I try to use the %s for the field name it puts single quotes around it. How can I prevent the single quotes around the field name? The sql statement will not execute with the single quotes?

$query = $wpdb->prepare("SELECT DISTINCT wp_geo.%s, wp_geo.$field2 FROM wp_geo WHERE wp_geo.$field3=%s",$field1, $typevalue);

Output with single quotes around 'county_short' field name:

SELECT DISTINCT wp_geo.'county_short', wp_geo.county_slug FROM wp_geo WHERE wp_geo.type='trailers'

I am using the below wpdb->prepare function in a sql statement. I am passing in several variables some field names and some values. When I try to use the %s for the field name it puts single quotes around it. How can I prevent the single quotes around the field name? The sql statement will not execute with the single quotes?

$query = $wpdb->prepare("SELECT DISTINCT wp_geo.%s, wp_geo.$field2 FROM wp_geo WHERE wp_geo.$field3=%s",$field1, $typevalue);

Output with single quotes around 'county_short' field name:

SELECT DISTINCT wp_geo.'county_short', wp_geo.county_slug FROM wp_geo WHERE wp_geo.type='trailers'

• mysql
• sql
• wpdb

Share Improve this question asked Jan 8, 2013 at 7:13 user1609391user1609391 47766 silver badges1010 bronze badges 1

• yes, for example, how can I avoid quotes on WHERE ID IN ('26975,27015,27016,27017,27018') ? $wpdb->prepare wraps my ids with quotes ! – gordie Commented Dec 12, 2018 at 0:04

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 1

You cannot suppress the quotes in $wpdb. Run mysqli_real_escape_string() in your script on these variables.

You can use %1s which excludes single quotes wrapper. By using %1s, you will note that the string won't be having single quotes and you will get the values directly as needed.

$query = $wpdb->prepare("
              SELECT DISTINCT wp_geo.%1s, wp_geo.%1s
              FROM wp_geo
              WHERE wp_geo.%1s = %1s",
              $field1, 
              $field2, 
              $field3, 
              $typevalue
         );

Note: Here, the sequence should follow for %1s and the variable you are providing after the query.

You can't use prepare() on column names, only on values.

本文标签： mysqlwpdbgtprepare function remove single quote for s in SQL statment