WordPress prepared statement with IN() condition
Try this code:
// Create an array of the values to use in the list
$villes = array("paris", "fes", "rabat");
// Generate the SQL statement.
// The number of %s items is based on the length of the $villes array
$sql = "
SELECT DISTINCT telecopie
FROM `comptage_fax`
WHERE `ville` IN(".implode(', ', array_fill(0, count($villes), '%s')).")
";
// Call $wpdb->prepare passing the values of the array as separate arguments
$query = call_user_func_array(array($wpdb, 'prepare'), array_merge(array($sql), $villes));
echo $query;
implode()
array_fill()
call_user_func_array()
array_merge()
Wordpress: wpdb prepare with conditional variable
Why not do the prepare in the "If" statement? You can then do the other prepare (without the where clause) in the "Else" and just use the get_results on the proper prepared query?
if($search_query!=="all") {
$search_query = '%' . $search_query . '%';
$where = 'WHERE column_name LIKE %s';
$prepared = $wpdb->prepare("SELECT * FROM {$wpdb->prefix}table_name ".$where." ORDER BY id DESC LIMIT %d, %d", $search_query, $current_page, $rows_per_page) ;
} else {
$prepared = $wpdb->prepare("SELECT * FROM {$wpdb->prefix}table_name ORDER BY id DESC LIMIT %d, %d", $current_page, $rows_per_page);
}
$results = $wpdb->get_results($prepared);
Wordpress : wpdb prepare with conditional
Pay attention to the use of the properties in the $wpdb object and the use of the prepare method, furthermore there is an issue with the column name for the post_status:
function test($array){
$sql = "SELECT SQL_CALC_FOUND_ROWS p.ID
FROM {$wpdb->posts} p
LEFT JOIN {$wpdb->postmeta} m ON m.post_id = p.ID
LEFT JOIN {$wpdb->term_relationships} r ON (p.ID = r.object_id)
LEFT JOIN {$wpdb->term_relationships} r1 ON (p.ID = r1.object_id)
LEFT JOIN {$wpdb->term_relationships} r2 ON (p.ID = r2.object_id)
LEFT JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = r.term_taxonomy_id
LEFT JOIN {$wpdb->terms} t ON t.term_id = tt.term_id AND t.term_id = r.term_taxonomy_id
WHERE 1=1";
if(isset($array['post_type']) && !empty($array['post_type']){
$sql .= $wpdb->prepare(" AND p.post_type = %s", $array['post_type']);
}
if(isset($array['post_type']) && !empty($array['post_type']){
$sql .= $wpdb->prepare(" AND p.post_status = %s", $array['post_status']);
}
$ids = $wpdb->get_col($sql);
return array_map('get_post', $ids)
}
Using like statement with $wpdb- prepare showing hashes where wildcard characters are
Don't worry about the hashes, they'll get replaced with % in $wpdb when you execute your query.
These hashes were introduced with WP v4.8.3 as a fix for SQL injection attack.
They're placeholders for the % character. It prevents someone from using something other than %s, %d, and %f. If there's a % other than those approved uses, it'll replace the % with a hash. That hash will get replaced back to % when $wpdb executes the query.
If you want to remove the hashes yourself, you can use remove_placeholder_escape()
, like so:
$query['conditions'][] = $wpdb->remove_placeholder_escape($wpdb->prepare($condition, $name));
Do I need to run a prepared query in Wordpress when I'm just matching table columns?
You dont need prepared statements there because there is no way that anyone could use this code for sql injection.
If you just select everything from two tables without giving any variable you can forget about prepared statements :)
What can I do to get actual prepared statements in Wordpress
History
Understand that the wpdb
class, originally a fork of Justin Vincent's ezSQL library, was introduced way back in 2003, only a couple of months after the MySQL prepared statement protocol was released and more than a year before PHP had any API support for accessing it: WordPress thus did the only thing it then could, and required any variables to be concatenated directly into dynamic SQL.
Back then, ezSQL (and thus WordPress) used PHP's mysql_escape_string()
function to escape such literals—and were therefore ignorant of the database connection's actual encoding (a threat that had only been addressed in PHP itself just a few months earlier).
This remained WordPress's (only) defence against SQL injection for nearly two years, whereupon it switched to using (the even less safe) addslashes()
for escaping—a situation that then remained in place for over four years until mysql_real_escape_string()
was finally utilised (albeit wpdb::prepare()
had been introduced in the interim)!
It then wasn't for another five years until WordPress even began using a driver capable of native statement preparation (i.e. MySQLi): this now just under two years ago and, as @naththedeveloper already pointed out, the discussion in ticket #21663 suggests that using native prepared statements may not even be on a to-do list.
Status Quo
Unfortunately, since the MySQLi object is a protected member of the wpdb
class for which there's no getter (and is assigned internally during instantiation), there's no way that you can use native prepared statements with WordPress's database connection (at least, not without modifying/replacing wpdb
with your own class).
Is there anything I can do to get the real thing?
You can, of course, open your own/separate connection to the database over which you have complete flexibility to do as you wish.
Or are my concerns unfounded?
As great as native statement preparation is (and I agree that it really should be the default defence strategy against SQL injection in any new project), it does also have its downsides. Careful escaping of one's variables for use as SQL literals (as done by PDO's prepared statement emulation, for example; and which is what wpdb::prepare()
these days attempts to deliver) should be resistant to all known attacks...
Wordpress query for IN operator with multiple AND in where clause
Try with this code, make sure that all placeholders are correct in your array while passing to the prepare
statement:
$how_many = count($usersRegisteFrOtherEvents);
$placeholders = array_fill(0, $how_many, '%d');
$format = implode(', ', $placeholders);
Related Topics
Avoiding Form Resubmit in PHP When Pressing F5
How to Return Back Twice in Laravel
Is MySQL_Insert_Id Safe to Use
Fastest Way to Add Prefix to Array Keys
Composer Killed While Updating
Why Is Textarea Filled with Mysterious White Spaces
Sanitizing User's Data in Get by PHP
Password_Hash Returns Different Value Every Time
How to Redirect After Download in Laravel
Simple PHP Contact Form with Firebase Hosting
How to Get Insert Id in Mssql in PHP
Pg_Query Result Contains Strings Instead of Integer, Numeric
Loading View Outside View Folder with Codeigniter
Split Alphanumeric String Between Leading Digits and Trailing Letters
Differencebetween the PHP Open Tags "<=" and "<Php"/"<"
Can You Re-Populate File Inputs After Failed Form Submission with PHP or JavaScript