$wpdb->prepare uses single quotes instead of backticks

  • Hello everybody,
    I’m writing a couple functions that should go and read data from a custom table I’ve set in the database, right now I’m just using $wpdb->get_var and $wpdb->get_row, both with $wpdb->preare to sanitize my code, but WP is telling me that I have an error in my mysql code, here’s a small example that is causing me trouble:

    global $wpdb;
$table_name = $wpdb->prefix . "tdg_market";
$total_customers = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %s", $table_name ) );

    And here’s what this code is returning:

    WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”wp_tdg_market” at line 1]
    SELECT COUNT(*) FROM 'wp_tdg_market'

    As you can see the table name is between single quotes as reported from the WordPress error. So I’ve tried this line:
    $total_customers = $wpdb->get_var( "SELECT COUNT(*) FROM wp_tdg_market” )
    without using $wpdb->prepare and hard coding the name of the table, and it worked like a charm, so I came to the conclusion that is $wpdb->prepare’s fault for using single quotes instead of backticks..

    Is there a way to make $wpdb->prepare use backticks instead of single quotes??

    Thanks a lot in advance

Viewing 2 replies - 1 through 2 (of 2 total)

  • I don’t think this is possible or even necessary.

    Preparing queries is meant to secure user input. Database table names as well as field names should (at least in general) not be called dynamically based on user input.

    So $total_customers = $wpdb->get_var( "SELECT COUNT(*) FROM " . $table_name ); should be just fine – as long as you don’t have any additional WHERE clauses or stuff like that (and besides the fact that it would be better to specify the fields you want to retrieve instead if using (*) :))

    Instead of
    $total_customers = $wpdb->get_var( $wpdb->prepare( “SELECT COUNT(*) FROM %s”, $table_name ) );
    Write
    $total_customers = $wpdb->get_var( $wpdb->prepare( “SELECT COUNT(*) FROM $table_name” ) );

    The printf replacements are meant for value replacement to prevent injection, not to replace tablenames.

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘$wpdb->prepare uses single quotes instead of backticks’ is closed to new replies.