$wpdb->prepare placeholders %d %s , working, but I am not convinced I have done it the best

Question

I have managed after a struggle to understand what is happening with the prepare placeholders. My only thought is that my table does not have a consistent element in it that I can use as a reference with the place holder.

There is a test column that I have used, but i do not intend on having it in my production plugin. The column is set to 0 for each entry, and I set the $test to 0. Thus my query has now started working. But this doesn't really make sense as a security feature unless it is dynamically calling something in reference to the results on the database. The examples I have seen around all rely on a set constant in their query, but I haven't got this unless I just add a constant entry in the database, but this seems silly.

$test = 0;
$result = 
$wpdb->get_results( $wpdb->prepare
    ( "SELECT * FROM $my_noted_table_name WHERE id_can_view = %d ", $test));

Is there a better way of doing this?

Thanks in advance..

Answer 1

Let me explain what is happening.

The prepare is sanitizing the variable's value, inserting it where you specified the placeholder, and then formatting the SQL query. Then the returned SQL query string is processed by the $wpdb->get_results().

Step 1:

For this line of code:

$wpdb->prepare( "SELECT * FROM $my_noted_table_name WHERE id_can_view = %d", $test );

here's what is happening:

1. Sanitizes the variable's value $test
2. Replaces out the placeholder with the sanitized variable's value.
3. The database table name is extracted from your $my_noted_table_name variable.
4. Formats the SQL query

For the placeholder, %d means the value will be an integer. If it's a string, then use %s instead. Think about it in terms of using the PHP construct sprintf or printf.

d - the argument is treated as an integer, and presented as a (signed) decimal number.

s - the argument is treated as and presented as a string.

So, let's say your variable $test has a value of 100 assigned to it and the database table's name is countries. Then SQL query string then is:

"SELECT * FROM `countries` WHERE `id_can_view` = 100;"

See how $wpdb->prepare transformed your inputted string into a properly formatted SQL query?

You want to ALWAYS use $wpdb->prepare() to handle this process as it will protect your database.