WPDB: Update table

Question

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

Answer 1

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.

Answer 2

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\\\"...