WordPress – How to protect your database queries using prepared statements

by

Prepared statements are a useful tool to protect against SQL injection attacks in software development – such as WordPress plugins and themes.

By using placeholders in the query string and passing the values separately, you can ensure that the values are properly sanitised and escaped before they are used in the database query.

This helps to prevent malicious users from inserting harmful SQL code into your database queries – such as accessing, deleting or modifying unexpected data.

How to create prepared database queries using WordPress?

To write a prepared database query using WordPress, you use the $wpdb global object and its prepare() method.

For example:

$query = $wpdb->prepare(
    "SELECT *
    FROM $wpdb->posts
    WHERE post_status = %s
    AND post_type = %s",
    'publish',
    'post'
);

In this example, the prepare() method is used to create a prepared statement that selects all published posts from the wp_posts table. The prepare() method takes two arguments:

  1. the query string that includes placeholders (%s) for the values that will be passed when the query is executed and
  2. an array of values to be passed to the query.

After the prepared statement is created, you can use the get_results() method to execute the query and retrieve the results:

$posts = $wpdb->get_results( $query );

In this example, the get_results() method is used to execute the prepared query and return the results as an array of objects.

How do prepared statements work?

Prepared database statements protect websites by preventing SQL injection attacks.

SQL injection is a type of security vulnerability that occurs when a malicious user is able to insert harmful SQL code into an existing database query.

This unexpected code can give the attacker access to sensitive data, modify or delete data, or take other malicious actions.

Prepared statements help to prevent SQL injection attacks by using placeholders (such as %s) in the query string to represent values that will be passed to the query when it is executed.

These placeholders act as a kind of “template” for the query, allowing you to specify the types and formats of the values that will be used without actually including the values in the query string.

When the prepared statement is executed, the values are passed to the query separately from the query string. This allows the software to properly sanitize and escape the values.

What types of text can be sanitised in prepared statements?

In addition to %s, there are other placeholders that can be used in prepared statements.

For example,

  • %d is used to represent an integer value,
  • %f is used to represent a floating-point value, and
  • %b is used to represent a binary value.

These placeholders allow you to specify the types of values that will be passed to the query, which helps to ensure that the values are properly formatted and handled by the database server.