0

I am currently developing my first WordPress plugin and am currently a bit confused on how to change a record in a database.

So far I have solved it using the $wpdb::update() function:

public function toggle_status() {
    global $wpdb;
    $id = (int) $_POST["id"];
    $active = (int) $_POST["active"];
    $tablename = $wpdb->prefix . 'myplugin_table';
    $wpdb->update($tablename, array("active" => $active), array("id" => $id)); // Update record
}

Now I have learned that the way I change the database is not safe regarding SQL injection. I should rather use the $wpml::prepare() function:

$wpdb->query($wpdb->prepare("UPDATE $tablename SET active = '%s' WHERE id = '%d'", array($active, $id)));

Is the $wpdb::update() function really not safe?

According to the documentation, this is not necessary for the $wpdb functions: "$data should be unescaped (the function will escape them for you). Keys are columns, Values are values." (https://codex.wordpress.org/Data_Validation#Database).

2 Answers 2

2

If you check the source code for $wpdb->update() the very last thing it does is prepare the query and then run it and return it.

So to answer your question, it is safe and the documentation is correct.

1

Now I have learned that the way I change the database is not safe regarding SQL injection.

So I wonder where/how did you learn that?

And other than that you should check if those two POST variables are actually set, your toggle_status() code looks fine to me, and $wpdb->update() is an easy way to update a record in the database in WordPress, so you should just use that function instead of having to use the "long" version: $wpdb->query( $wpdb->prepare( "UPDATE ...", ... ) ).

And actually, if you were to use that version, then the correct syntax is:

$wpdb->query(
    $wpdb->prepare( "UPDATE $tablename SET active = %s WHERE id = %d", $active, $id )
);

I.e. Do not wrap the query value placeholders in quotes, e.g. just %s and not '%s', and for each placeholder, pass its replacement value as a direct parameter for $wpdb->prepare() and not as part of the array like you did in your code.

And yes, with $wpdb->update(), you should pass the raw data and not escaped ones, e.g. pass "foo" as-is and not escaped like \"foo\". Otherwise, the function would double-escape that escaped value as \\\"foo\\\"...

3
  • I had contact with the WordPress plugin team and they wrote: "And this: $status = $wpdb->update($tablename, array("active" => $active), array("id" => $id)); needs to use prepare(). You should use update AND prepare: $wpdb->update( $wpdb->prepare( .... ) )".
    – Jonas
    Mar 23, 2021 at 1:41
  • 1
    That's why I'm so confused. Also, according to the documentation, the $wpdb->update($wpdb->prepare(...)) function shouldn't work at all.
    – Jonas
    Mar 23, 2021 at 1:43
  • In that case, then I would ask for their clarification on how exactly should I use $wpdb->prepare() with that $wpdb->update(). Or ask them, "can't I just use $wpdb->update() and the documentation says the data should be NOT escaped?".. You can also send them to this very page, if you want to..
    – Sally CJ
    Mar 23, 2021 at 1:53

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.