1

I am using custom php code to perform data insertion, deletion, updating and other tasks. I am able to insert data into a table in two different ways,

$wpdb->insert($table_name, array('id' => NULL, 'name' => '$name', 'email' => '$email', 'city' => '$city'));

and

$sql = "INSERT INTO $table_name VALUES('', '$name', '$email', '$city')";
$wpdb->query($sql);

Is it a good practice to use wpdb->query() function each time by passing my query to the function instead of using the dedicated functions like insert() and delete() etc? If not, what are the disadvantages of this approach?

2 Answers 2

7

If you look a bit into the source, you'll see that $wpdb->insert() will use ->query() under the hood. So should be the same, right?

Not that simple. It doesn't just use ->query() but also ->prepare(), which is considered best practice. Meanwhile with your code example you've probably just opened yourself to SQL injections.

The takeaway here: If it is a simple operation and the ->insert() etc. method work - use them. They're tested and contain little risk. Writing your own queries always carries the risk of opening yourself up to troubles such as SQL injections.

2
  • Simple but useful.... One question, how do these built in functions provide protection against SQL injections?
    – Asmat Ali
    Dec 9, 2020 at 15:13
  • 1
    @AsmatAli They use prepare(), which takes care of escaping input. If you use your favorite search engine, you should find enough topics around the topic of "prepared statements"
    – kero
    Dec 9, 2020 at 16:16
5

Is it a good practice to use wpdb->query() function each time by passing my query to the function instead of using the dedicated functions like insert() and delete() etc?

No, it is not good practice. Always prefer the insert/update/etc methods.

Generally the use of raw SQL in WordPress is a code smell, and using raw SQL when a helper function is available is also bad practice. It implies that the writer is unaware of more convenient APIs with better speed/security, and forces you to reinvent all the fixes and bugs WP developers encountered over the years.

So:

  • Use insert etc to insert, not query, always prefer the more specific function
  • Use the 3rd parameter that specifies the format for security reasons
  • use query as a fallback for more general SQL queries where a specific function is unavailable
  • Don't wrap variables in ', use $name not '$name' or you'll insert $name not its value.
  • you don't need the id set to null, if the table was created right with auto increment you can remove that
  • you should check if it succeeded or not, don't just assume it worked
$result = $wpdb->insert(
    $table_name,
    [
        'name'  => $name,
        'email' => $email,
        'city'  => $city,
    ],
    [
        '%s',
        '%s',
        '%s',
    ]
);
if ( false === $result ) {
    // something went wrong
}

What About Creating Tables?

Use dbDelta, it will create tables if they don't exist, and update their schema if it changes. No queries to test if the table exists, or update it, or create it, dbDelta does it for you. It takes a table creation query as a parameter, and it has to be formatted in a particular way or it won't work.

wpdb->query is inappropriate for creating tables.

A Note on Custom Tables

Just because you used a custom table doesn't mean it's faster/better. Make sure you design your tables with keys and indexes that reflect the types of queries you're going to run. A well built table can be lightning fast, but most tables will perform worse the custom post types at scale due to bad design.

And where possible, avoid writing SQL to interact with your table if you can.

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.