37

I'm new to SQL and am wondering if I need to use wpdb->prepare for the following query to a table I've created

global $wpdb;
$tablename = $wpdb->prefix . "my_custom_table";
$sql = "SELECT * FROM " . $tablename . " ORDER BY date_created DESC";
$resulst = $wpdb->get_results( $sql , ARRAY_A );

Do I need to use prepare here? How would I do that?

Cheers

3 Answers 3

45

It's best practice to always use prepare but the main use of it is to prevent against SQL injection attacks, and since there is no input from the users/visitors or they can't effect the query then that is not an issue in your current example.

But like I said before it's best practice to use it and once you start using it you never stop, so in your example you can use it like so:

global $wpdb;
$tablename = $wpdb->prefix . "my_custom_table";
$sql = $wpdb->prepare( "SELECT * FROM %s ORDER BY date_created DESC",$tablename );
$results = $wpdb->get_results( $sql , ARRAY_A );

to read more about how to use it head to the codex

7
  • Hi @Bainternet, thanks for such a clear explanation - for some reason when I try your code it returns an empty array. I've checked and doubled checked for typos. If I do the unprepared query I get the array. I don't understand why it's not working..! May 11, 2011 at 8:03
  • Odd. I've tried using the same code with another query: $tablename = $wpdb->prefix . "my_custom_table"; $concert_id = 1; $sql = "SELECT * FROM " . $tablename . " WHERE concert_id = %d LIMIT 1;"; $prep_sql = $wpdb->prepare( $sql, $concert_id ); $get_concerts = $wpdb->get_results( $prep_sql , ARRAY_A ); And it works great! Not sure why that would be. But I get it now in any case! May 11, 2011 at 8:36
  • 9
    Enclosing the table name in single quotes will not work. The normal escaping is with backticks, so your query should end up looking like this: SELECT * FROM `wp_my_custom_table`. You can enable double quote support, but then it would have to look like this: SELECT * FROM "wp_my_custom_table".
    – Jan Fabry
    May 11, 2011 at 8:52
  • 3
    I don't agree with this answer. Why you should escape when function already escapes everything? You think Wordpress will decide to remove escaping from core? ALso there is no point to escape table name :) because it's hardcoded and you know it's ok. I kno this is just example but anyways dont escape table names, i have issues when using prepare with table names it adds backticks and SQL trows error.
    – Tommixoft
    Nov 4, 2014 at 12:41
  • @Tommixoft If you read the answer again you'll see that you actually say the same thing as I said, and that the table name is an example.
    – Bainternet
    Nov 5, 2014 at 7:07
0

When you use prepare it is protecting the code from SQL injection vulnerabilities.

Here is the code you need to modify for using prepare();

global $wpdb;
$tablename = $wpdb->prefix . "my_custom_table";
$sql = $wpdb->prepare( "SELECT * FROM {$tablename} ORDER BY date_created DESC");
$resulst = $wpdb->get_results( $sql , ARRAY_A );
2
  • 3
    Just because you use prepare() the code is not automatically protected against SQL injections. Your code might suggest that $tablename is protected against SQL injections because it is used in prepare which is not the case, because it is not provided via the arguments. In this case that is not a problem, because the content of $tablename is safe, but I think it would be good to clearify that.
    – Constantin
    Dec 28, 2020 at 15:04
  • 1
    Yes. This answer is wrong and dangerously misleading I would say. You've called prepare in a way which does not in fact protect against SQL injection.
    – Harry Wood
    Nov 11, 2021 at 11:30
0

In your case is not possible SQL injection attack. Your code don't need additional protection because don't use user input like: post, get, request, cookie.

Don't use complicated function when are not necessary to save server resources.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.