Download the PHP package snicco/better-wpdb without Composer

BetterWPDB - Keeps you safe and sane when working with custom tables in WordPress.

BetterWPDB is a small class with zero dependencies that uses the default mysqli connection created by WordPress.

Table of contents

1. Why you should use this
   1. wpdb uses no prepared statements
   2. wpdb has horrible error handling
   3. wpdb is "slow"
   4. wpdb is verbose
   5. wpdb returns everything as strings
   6. static analyzers don't like wpdb
2. Installing
   1. composer
   2. setup
3. Usage
   1. Running prepared queries
   2. Selects
      1. select
      2. selectAll
      3. selectRow
      4. selectValue
      5. selectLazy
      6. batchProcess
      7. exists
   3. Inserts
      1. insert
      2. bulkInsert
   4. Updates
      1. update
      2. update by primary key
   5. Deletes
   6. Transactions
   7. Logging
   8. Query Builder
4. Contributing
5. Issues and PR's
6. Security

Why you should use this

The motivation for this library is best explained with simple examples. Let's assume we have the following custom table in your database.

`

• A unique string with max 10 chars
• A float column
• An unsigned integer column
• A boolean column

wpdb does not use prepared statements

Besides, what wpdb::prepare() has you thinking, wpdb is NOT using prepared statements. Explaining the differences is beyond the scope of this README but as a recap:

When using prepared statements, the sql query and the actual values are sent separately to your database. It's thus impossible to perform any SQL injection.

wpdb::prepare() is a string escaper. The name is misleading and its utility is suboptimal.

You can read more about this topic and why it's so important to use real prepared statements here:

• Disclosure: WordPress WPDB SQL Injection - Technical
• The Hitchhiker's Guide to SQL Injection prevention
• On the (in)security of popular open source Content Management Systems
• Preventing SQL Injection in PHP Applications

wpdb has horrible error handling

The error handling in the wpdb class is pretty much non-existent. And in case wpdb fails, it does so gracefully. However, there is no way to recover from a database error as your application is in unknown state, so you want your database layer to fail loud and hard.

1. Lets compare error handling for totally malformed SQL.

   wpdb will return (bool) false for failed queries which causes you to type-check the result or every single sql query only to (hopefully) throw an exception afterwards.

2. Inserting data that is too big for the defined column.

   Remember, in our database table definition we did set test_string column to a varchar(10).

3. Inserting flat-out wrong data

   We defined test_int as an unsigned integer. Let's see what happens if we try to insert a negative number.

4. wpdb can only print errors as html and can only log to the configured error_log destination

   If wpdb manages to catch a totally wrong db error (and you have show_errors turned on) wpdb will just echo the output as html ( very usefully during unit tests and rest api calls). Error logging is hardcoded, good luck sending db errors to Sentry, New Relic or using any PSR logger.

wpdb is "slow"

This ties in directly to the graceful error handling.

❌ Before every single query wpdb will check the query against the table/column charset and collation. wpdb will also validate data for write operations against the data you provided by fetching the full table info. If a query is deemed not compatible (bool) false is returned, and you will never now about it.

✅ Just set the charset and collation once for connection and let mysql handle what it can already handle.

wpdb is verbose, easy to misuse and hard to debug.

The API of wpdb is needlessly verbose. Furthermore, It's hard to use correctly and easy to use wrong.

The amount of code in WordPress plugins that looks something like this is shocking.

If you don't know why this is bad stop here and read this article by PHP core contributor Anthony Ferrara .

"The current system is insecure-by-design. That doesn’t mean it’s always hackable, but it means you have to actively work to make it not attackable. It’s better to switch to a design that’s secure-by-default and make the insecure the exceptional case."

wpdb returns everything as strings

static analysers like Psalm and PHPStan have trouble understanding wpdb.

This ties into the error handling where different values are returned based on failure or success. Let's compare the return signature of wpdb and better_wpdb:

Installing

You can install BetterWPDB via composer. The only requirement is php: ^7.4|^8.0. There are no further dependencies.

composer

`

setup

BetterWPDB DOES NOT open a second connection to your database. All you have to do to start using it is the following:

Optionally you can also pass an already connected mysqli instance (in case you are connecting to a secondary database etc.)

Usage

Running prepared queries

If you need full control of your sql query or have a complex use case you can directly use the low-level preparedQuery method. This method will return an instance of mysqli_stmt. For most use cases there are more high level methods available.

!Important: If you are using the preparedQuery AND your query is a SELECT query, you need to manually restore the default error handling.

All other methods take care of this automatically.

❌ Never pass ANY user input into the first argument of preparedQuery

✅ Use "?" placeholders for user input and pass in an array of values.

❌ Never allow users to provide table names, column names, order by values or similar

If you follow these three simply rules you are 100% safe from any sql-injections.

Selects

select

The most low-level select method. Returns an instance of mysqli_result

selectAll

Returns an array or all matching records.

This method is preferred for smaller result sets. If you need to query a lot of rows using selectLazy is preferred.

selectLazy

Occasionally you will need to query a lot of records from your database to process them in some form. A typical use-case would be exporting 100k orders into a CSV file. If you try to use selectAll for this you will be out of memory immediately.

This is where the selectLazy method is extremely useful. It returns a PHP Generator that has always only 1 row in memory.

batchProcess

The batchProcess method can be used if you need to select a large amount of records that you need to update depending on logic that can only be performed in PHP code.

This method will perform highly performant keyset pagination.

It is essential that the sorting columns of the query ensure a deterministic sorting order. Furthermore, the sorting column values should not be updated inside the batch process callback.

Read more about the underlying keyset pagination here and here (Highly recommended read for this method effectively)

The below code showcases how all users' password can be reset. For more examples, have a look at the test cases here.

selectRow

Returns the first row that matches the provided query. Throws an instance of NoMatchingRowFound if no row can be found.

selectValue

Selects a single value from or throws an exception if no rows are found.

exists

You can use this method to check if a record exists in the database

❌ Never allow user input as keys for the array.

Inserts

insert

Inserts a single row into the database and returns an instance of mysqli_stmt

❌ Never allow user input as keys for the array.

bulkInsert

A common use case is inserting multiple records at once and ensuring that either all records are inserted or none.

Think importing a csv of members into your database. You don't want 5 inserts to fail and 5 to succeed. This method helps you achieve this. All inserts will be performed inside a database transaction that will automatically commit on success or roll back if any errors happen.

❌ Never allow user input as keys for the array.

You can pass any iterable into bulkInsert.

This is how you import a huge CSV file into your database without running out of memory.

``

Updates

updateByPrimary

Updates a record by its primary key. By default, it will be assumed that the primary key column name is id.

❌ Never allow user input as keys for the array.

update

A generic update method. The second argument is an array of conditions, the third argument an array of changes.

❌ Never allow user input as keys for the conditions

❌ Never allow user input as keys for the changes

Deletes

delete

Deletes all records that match the provided conditions.

❌ Never allow user input as keys for the conditions

Transactions

Unfortunately, database transactions are used very rarely in WordPress plugins. A transaction ensures that either all or db queries inside the transaction succeed or all fail.

Typical code you find in many WordPress plugins:

Logging

You can a second argument to the constructor of BetterWPDB.

Implement the simple QueryLogger interface and start logging your database queries to your favorite profiling service.

The following is pseudocode to log to New Relic:

``

Query Builder

BetterWPDB is not a query builder and unless you query is dynamic you don't need one.

Most of the time plain sql-queries are more readable and easier to debug.

If some of your queries are highly dynamic you can consider using latitude which is a full-blown query builder that works perfectly with BetterWPDB.

composer require latitude/latitude

Contributing

This repository is a read-only split of the development repo of the Snicco project.

This is how you can contribute.

Reporting issues and sending pull requests

Please report issues in the Snicco monorepo.

Security

If you discover a security vulnerability within BetterWPDB, please follow our disclosure procedure.