My custom sql query for getting a username giving me a warning and an error

Question

I am writing a custom query to create a link to a specific username. A user will be logged in and can then click on a link to that user name. I am getting a warning and an error running the query in php.

The warning is:

Missing argument 2 for wpdb::prepare(), called in /home/..../public_html/wp-content/plugins/insert-php-code-snippet/shortcode-handler.php(72) : eval()'d code on line 22 and defined

The error is:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'as a join as b on a.id = b.customer_id join as c on b.appointment_id = c.id ' at line 1

My query:

global $wpdb;
  //the query

    $currentuserid = wp_get_current_user();
        $havelesson = $wpdb->query( 
            $wpdb->prepare( 
                "
SELECT e.user_login FROM $wpdb->ab_customers  as a
join $wpdb->ab_customer_appointments as b on a.id = b.customer_id
join $wpdb->ab_appointments as c on b.appointment_id = c.id
join $wpdb->ab_staff as d on b.appointment_id = c.id
join $wpdb->users as e on d.wp_user_id = e.id
where a.wp_user_id = $currentuserid->ID
and start_date > now()
order by start_date asc
Limit 1
"   
)
 );

What could be causing the error and warning?

Answer 1

The Warning

Missing argument 2 for wpdb::prepare(), called in /home/..../public_html/wp-content/plugins/insert-php-code-snippet/shortcode-handler.php(72) : eval()'d code on line 22 and defined

It looks like there's been a fundamental misunderstanding of what the prepare method is supposed to do. You've provided a single parameter to the function, but it expects multiple. Hence the missing argument 2 warning.

What Does prepare do then?

Imagine the following query that searches for posts with a particular title:

$wpdb->query( "SELECT * FROM $wpdb->posts WHERE post_title=$title" );

Something is terribly wrong with this code. It's horrifically insecure. To demonstrate why, consider the user searched for this:

$title = '*;SELECT user_password FROM wp_users WHERE username = "admin"';
$wpdb->query( "SELECT * FROM $wpdb->posts WHERE post_title=$title" );

Now we have an SQL injection attack. prepare is meant to allow us to insert variables while keeping the query safe from these kinds of values.

For example:

$query = $wpdb->prepare(
    "SELECT * FROM $wpdb->posts WHERE post_title=%s",
    array( $title )
);
$wpdb->query( $query );

Now our query is safe. Your query has this issue too because of the incorrect use of wpdb prepare. As a result, the following variables are wide open to SQL attacks:

• $wpdb->ab_customers
• $wpdb->ab_customer_appointments
• $wpdb->ab_appointments
• $wpdb->ab_staff
• $wpdb->users
• $currentuserid->ID

That's why all the examples use $wpdb->prepare and that's why you need additional arguments.

The Error

Now that we know how to secure the query, that still leaves another problem:

The query isn't valid SQL

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'as a join as b on a.id = b.customer_id join as c on b.appointment_id = c.id ' at line 1

It's this as a join syntax that has the problem, it's not something I have encountered, and appears to be made up.

This is what a JOIN should look like:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

See W3 School for more information, and keep in mind that this isn't a generic SQL stack exchange, pure SQL questions such as this aren't best asked here

A Note on SQL and WPDB

I get the overwhelming feeling that the original sin here was that all of this could have been done with date archives, author archives, custom post types, and custom taxonomies.

E.g. if you had used a custom post type for customer appointments, and set the author of those posts to the user who the appointment belonged to, then you would have gotten author archives for free, giving you:

• a custom archive-customer_appointment.php template
• Archives at example.com/customer_appointment/, URL configurable via register_post_type
• Author archives, and easy peasy URLs via get_author_link
• You could use pre_get_posts to filter it all so users only see their own stuff
• You could then use WP_Query and the entire WP_Cache toolchain would have automatically sped everything up
• Caching plugins and object drop ins would suddenly work
• Free REST API endpoints for your javascript
• A free Admin UI for editing and administering all this data