MySQL and WordPress – How to use native queries in a secure and right way

WordPress requires a MySQL instance to run and store all the data. Although the relational system is a classic and, sometimes, obsolete data structure, MySQL works like a charm on a WordPress environment.

Keeping this on the WordPress core, gave us better compatibility with our custom development over time and quick and easy access to the data. However, rare times developers access the data using MySQL native queries – normally, the WP methods are enough for a straightforward development (New WP_Query, get_posts, etc…). But if you need deeper access to the data, SQL Queries need to enter on your WP package-skills. High-level SQL knowledge isn’t needed, but knowing some methods will be great for your future developments.

Consider you already have some skills using SQL statements, I would like to show you the best secure way to work with it using WordPress Best Practices. Please follow me:

WordPress Database Class

WordPress core already has a class to interact with the database, it’s a global object: $wpdb. Don’t ignore it, this class is the safer and better way to access the database directly. If you need to extend the class for your specific needs, you can replace it in wp-content/db.php. Anyway, I strongly suggest only use this alternative if the existing methods don’t be enough.

Sanitize and Escape everything

2021 and SQL injection still exists. Using $wpdb, we just need to use the “prepare” method to prevent possible SQL attacks.

$sql = $wpdb->prepare( 'SELECT * from %s' , $table_name );

Pretty simple and supports placeholders! After sanitizing and escaping our SQL query, we’re able to run the query.

Query Methods

I recommend you three main methods to run queries, depending on what you need to do with the data: consult, write, delete…

  1. get_results()
    One of my favourites, this function is used for getting an array with query results. Perfect for “SELECT” statements.
  2. get_var()
    Same as get_results(), but better for receiving a single value – a custom meta, for example. The returned value is a string, instead of an array.
  3. query()
    Use this when you need to write on the database. The function will return a true/false.

A Complete Example

Imagine you want to list all the WP users on an array. The best way to do that is:

// first, call the object to be accessible
global $wpdb;

// don't forget to use the ->prefix for a dynamic table name
$users_table = $wpdb->prefix . 'users';

// your safe query
$query      = $wpdb->prepare( 'SELECT * FROM %s', $users_table );
$users_list = $wpdb->get_results( $query );

That’s it. I recommend you read more about the $wpdb object and be prepared to write safer and more performative SQL queries on WP. Use the comments box for additional questions or suggestions!


Posted

in

, , ,

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *