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).

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 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 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 at 1:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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