Recently, I was involved in an interesting personal project where I assisted my friend in building a WordPress website with a couple of custom database tables. This was intended to support the functionality of his property listing website.
There are many ways of retrieving data from a WordPress database such as WP_Query, get_posts(), wp_get_post_terms(), etc. The default WordPress function to interact with a database is the recommended function because it has better performance, such as database caching. You can find the full code reference for this here. Unfortunately for custom database tables, you will need to call the WordPress database directly using $wpdb.
The $wpdb Class
WordPress provides a set of functions to interact with databases using the $wpdb object. Since it’s a global object you can call this function anywhere using global declaration.
global $wpdb;
You can see a full explanation of wpdb on the WordPress codex site. Here is an example of how to access the wpdb object:
$id = $_GET['id']; $results = $wpdb->get_results( "SELECT * FROM $wpdb->property_listing WHERE ID = $id" );
If you think this code is fine though, think again! This code has an SQL Injection vulnerability because this query lets visitors interact with databases directly without sanitisation, if the $_GET[‘id’] is an absolute number then it’s fine, but a visitor could easily add evil code here.
So what is the better approach to interact with WordPress database?
$id = (int) $_GET['id']; $results = $wpdb->get_results( "SELECT * FROM $wpdb->property_listing WHERE ID = $id" );
The above query is relatively safe but still not recommended. You don’t have to sanitise the input by your code because WordPress provides a better sanitising using prepare() function.
The right way to interact with WordPress database!
$id = $_GET['id']; $results = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM $wpdb->property_listing WHERE ID = %d", $id ) );
The prepare() function will complete several checks and if it’s not an integer (for our example) then it will end up calling a function named mysql_real_escape_string(). Please not that %d is for integer data input and %s is for string data.
Here is the example for combining both string and integer:
$id = $_GET['id']; $string = $_GET['location']; // South Australia $results = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM $wpdb->property_listing WHERE ID = %d AND location = %s", $id, $string ) );
So whenever you need to interact with database directly, always use the prepare() function.
WordPress now powers one-third of all websites, that means 33% of all websites on the internet are run by WordPress. We are, as WordPress developers, proud of this fact. However, this also make WordPress the most targeted site for hackers, as you can see from the Sucuri 2018 report. Let’s makeWordpress a better place by writing safe code!
