PHPackages                             snicco/better-wpdb - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. snicco/better-wpdb

ActiveLibrary[Database & ORM](/categories/database)

snicco/better-wpdb
==================

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

v1.10.1(1y ago)4323.3k↓17.3%84LGPL-3.0-onlyPHPPHP ^7.4|^8.0

Since Apr 17Pushed 1y ago6 watchersCompare

[ Source](https://github.com/snicco/better-wpdb)[ Packagist](https://packagist.org/packages/snicco/better-wpdb)[ RSS](/packages/snicco-better-wpdb/feed)WikiDiscussions master Synced 2d ago

READMEChangelogDependencies (2)Versions (35)Used By (4)

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

[](#betterwpdb---keeps-you-safe-and-sane-when-working-with-custom-tables-in-wordpress)

[![codecov](https://camo.githubusercontent.com/a99e6ec528fffd1664e95534f9a09a4a09d2afe62799ff0d8774dc22d8453f6c/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f436f7665726167652d3130302532352d73756363657373)](https://codecov.io/gh/snicco/snicco)[![Psalm Type-Coverage](https://camo.githubusercontent.com/c12cfed65c7da16501f7a84e7861b8c4757fc30e9dc00bb2983783dbb3f3f84c/68747470733a2f2f73686570686572642e6465762f6769746875622f736e6963636f2f736e6963636f2f636f7665726167652e7376673f)](https://shepherd.dev/github/snicco/snicco)[![Psalm level](https://camo.githubusercontent.com/c5e90ffcf3a5aa1f78f93bddde5db7627b114329393aa87697df8cedc7f5391a/68747470733a2f2f73686570686572642e6465762f6769746875622f736e6963636f2f736e6963636f2f6c6576656c2e7376673f)](https://psalm.dev/)[![PhpMetrics - Static Analysis](https://camo.githubusercontent.com/364ffb28ea219affd0fed2e99cc046bac0bf41da3f1d3814e0cbe4a4bb54c994/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f5068704d6574726963732d5374617469635f416e616c797369732d326561343466)](https://snicco.github.io/snicco/phpmetrics/BetterWPDB/index.html)[![PHP-Versions](https://camo.githubusercontent.com/241a10d25aa09d5e8a82ebd2b55780a63dd43736d958d4004c3166e650874aca/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f5048502d253545372e34253743253545382e30253743253545382e312d626c7565)](https://camo.githubusercontent.com/241a10d25aa09d5e8a82ebd2b55780a63dd43736d958d4004c3166e650874aca/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f5048502d253545372e34253743253545382e30253743253545382e312d626c7565)

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

Table of contents
-----------------

[](#table-of-contents)

1. [Why you should use this](#why-you-should-use-this)
    1. [wpdb uses no prepared statements](#wpdb-does-not-use-prepared-statements)
    2. [wpdb has horrible error handling](#wpdb-has-horrible-error-handling)
    3. [wpdb is "slow"](#wpdb-is-slow)
    4. [wpdb is verbose](#wpdb-is-verbose-easy-to-misuse-and-hard-to-debug)
    5. [wpdb returns everything as strings](#wpdb-returns-everything-as-strings)
    6. [static analyzers don't like wpdb](#static-analysers-like-psalm-and-phpstan-have-trouble-understanding-wpdb)
2. [Installing](#installing)
    1. [composer](#composer)
    2. [setup](#setup)
3. [Usage](#usage)
    1. [Running prepared queries](#running-prepared-queries)
    2. [Selects](#select)
        1. [select](#select)
        2. [selectAll](#selectAll)
        3. [selectRow](#selectRow)
        4. [selectValue](#selectvalue)
        5. [selectLazy](#selectlazy)
        6. [batchProcess](#batchprocess)
        7. [exists](#exists)
    3. [Inserts](#inserts)
        1. [insert](#insert)
        2. [bulkInsert](#bulkinsert)
    4. [Updates](#updates)
        1. [update](#update)
        2. [update by primary key](#updatebyprimary)
    5. [Deletes](#deletes)
    6. [Transactions](#transactions)
    7. [Logging](#logging)
    8. [Query Builder](#query-builder)
4. [Contributing](#contributing)
5. [Issues and PR's](#reporting-issues-and-sending-pull-requests)
6. [Security](#security)

Why you should use this
-----------------------

[](#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.

```
'CREATE TABLE IF NOT EXISTS `test_table` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `test_string` varchar(10) COLLATE utf8mb4_unicode_520_ci UNIQUE NOT NULL,
  `test_float` FLOAT(9,2) UNSIGNED DEFAULT NULL,
  `test_int` INTEGER UNSIGNED DEFAULT NULL,
  `test_bool` BOOLEAN DEFAULT FALSE,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;', []
);
```

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

### wpdb does not use prepared statements

[](#wpdb-does-not-use-prepared-statements)

---

Besides, what [`wpdb::prepare()`](https://developer.wordpress.org/reference/classes/wpdb/prepare/) has you thinking, [`wpdb`](https://developer.wordpress.org/reference/classes/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()`](https://developer.wordpress.org/reference/classes/wpdb/prepare/) is a [string escaper](https://github.com/WordPress/WordPress/blob/master/wp-includes/wp-db.php#L1395). 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](https://blog.ircmaxell.com/2017/10/disclosure-wordpress-wpdb-sql-injection-technical.html#The-Correct-Fix)
- [The Hitchhiker's Guide to SQL Injection prevention](https://phpdelusions.net/sql_injection)
- [On the (in)security of popular open source Content Management Systems](https://paragonie.com/blog/2016/08/on-insecurity-popular-open-source-php-cms-platforms#wordpress-prepared-statements)
- [Preventing SQL Injection in PHP Applications](https://paragonie.com/blog/2015/05/preventing-sql-injection-in-php-applications-easy-and-definitive-guide)

```
❌ // This is not a prepared query

$wpdb->get_results(
    $wpdb->prepare('select * from `test_table` where `test_int` = %d and `test_string` = %s', [1, 'foo'])
);

✅ // This is a "real" prepared query

$better_wpdb->preparedQuery('select * from `wp_users` where `id` = ?' and `test_string` = ?, [1, 'foo']);
```

### `wpdb` has horrible error handling

[](#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.](https://phpdelusions.net/articles/error_reporting)

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.

```
❌ // This is what you typically see in WordPress code

$result = $wpdb->query('apparently not so valid sql');

if($result === false) {
    throw new Exception($wpdb->last_error);
}
```

```
✅ // This is how it should be

$result = $better_wpdb->preparedQuery('apparently not so valid sql');

// You will never ever get here.

var_dump($e->getMessage()) // You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'apparently not a valid SQL statement' at line 1
                           // Query: [apparently not a valid SQL statement]
                           // Bindings: []
```

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)`.

```
❌

$result = $wpdb->insert('test_table', [
    // the limit is 10, we insert 11 chars
    'test_string' => str_repeat('X', 11)
])

var_dump($result) // (bool) false
var_dump($wpdb->last_error); // WordPress database error: Processing the value for the following field failed: test_string.
                             // The supplied value may be too long or contains invalid data.

// Notice that there is no mention of the invalid query or what type of data was inserted
// As a little benefit you also have nothing in your error log.
```

```
✅

$result = $better_wpdb->insert('test_table', [
     'test_string' => str_repeat('X', 11)
])

// You will never ever get here.

var_dump($e->getMessage()) // Data too long for column 'test_string' at row 1
                           // Query: [insert into test_table (test_string) values(?)]
                           // Bindings: ['XXXXXXXXXXX']

// This exception message is automatically logged.
```

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.

```
❌

$result = $wpdb->insert('test_table', [
    'test_string' => 'bar'
    'test_int' => -10
])

var_dump($result) // (bool) true
var_dump($wpdb->last_error); // ''

// Congratulations. Your database now contains invalid data and you will never know about it.
```

```
✅

$result = $better_wpdb->insert('test_table', [
     'test_string' => 'bar'
     'test_int' => -10
])

// You will never ever get here.

var_dump($e->getMessage()) // Out of range value for column 'test_int' at row 1
                           // Query: [insert into test_table (test_string, test_int) values (?,?)]
                           // Bindings: ['bar', -10]

// This exception message is automatically logged.
```

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](https://github.com/WordPress/wordpress-develop/blob/5.9/src/wp-includes/wp-db.php#L65) turned on) wpdb will just [`echo` the output as html](https://github.com/WordPress/wordpress-develop/blob/5.9/src/wp-includes/wp-db.php#L1608) ( very usefully during unit tests and rest api calls). Error logging is [hardcoded](https://github.com/WordPress/wordpress-develop/blob/5.9/src/wp-includes/wp-db.php#L1582), good luck sending db errors to **Sentry**, **New Relic** or using any PSR logger.

### `wpdb` is "slow"

[](#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.

[](#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](https://wordpress.stackexchange.com/search?q=prepare).

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

```
❌

$where = "WHERE foo = '" . esc_sql($_GET['data']) . "'";
$query = $wpdb->prepare("SELECT * FROM something $where LIMIT %d, %d", 1, 2);
$result = $wpdb->get_results($query);

✅

$result = $better_wpdb->selectAll('select * from something where foo = ? LIMIT ?', [1, 2]);
```

If you don't know why this is bad stop here and [read this article by PHP core contributor Anthony Ferrara](https://blog.ircmaxell.com/2017/10/disclosure-wordpress-wpdb-sql-injection-technical.html).

> "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

[](#wpdb-returns-everything-as-strings)

---

```
$wpdb->insert('test_table', [
    'test_string' => 'foo',
    'test_int' => 10,
    'test_float' => 20.50,
    'test_bool' => true
])

❌

$row = $wpdb->get_row($wpdb->prepare('select * from test_table where test_string = %s', 'foo'));

var_dump($row['test_string']); // (string) foo
var_dump($row['test_int']); // (string) 1
var_dump($row['test_float']); // (string) 20.50
var_dump($row['test_bool']); // (string) 1

✅

$row = $better_wpdb->selectRow('select * from test_table where test_string = ?', 'foo');

var_dump($row['test_string']); // (string) foo
var_dump($row['test_int']); // (int) 1
var_dump($row['test_float']); // (float) 20.50
var_dump($row['test_bool']); // (int) 1
```

### static analysers like Psalm and PHPStan have trouble understanding wpdb.

[](#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:

```
❌ // The abbreviated phpdoc of wpdb::get_row
   // This method has 4 different return types? Also, what is return void?

/**
*
* @param string|null $query
* @param string $output
* @param int $y
* @return array|object|null|void Database query result in format specified by $output or null on failure.
*/
public function get_row($query = null, $output = OBJECT, $y = 0) {
   //
 }

✅  // Your favorite static analysis tool will thank you.

 /**
 * @param non-empty-string $sql
 * @param array $bindings
 *
 * @return array
 *
 * @throws NoMatchingRowFound
 * @throws QueryException
 * @throws InvalidArgumentException
 */
 public function selectRow(string $sql, array $bindings): array {
    //
 }
```

Installing
----------

[](#installing)

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

### composer

[](#composer)

```
composer require snicco/betterwpdb
```

### setup

[](#setup)

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

```
// require composer autoloader

use Snicco\Component\BetterWPDB\BetterWPDB;

$better_wpdb = BetterWPDB::fromWpdb();
```

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

```
// require composer autoloader

use Snicco\Component\BetterWPDB\BetterWPDB;

$mysqli = /* ... */

$better_wpdb = new BetterWPDB($mysqli);
```

Usage
-----

[](#usage)

### Running prepared queries

[](#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`](https://www.php.net/manual/de/class.mysqli-stmt.php). 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.

```
use Snicco\Component\BetterWPDB\BetterWPDB;

$mysqli = /* ... */

$better_wpdb = new BetterWPDB($mysqli);

// Only for select queries.
$auto_restore_error_handling = false;

// stmt is an instance of mysqli_stmt
$stmt = $better_wpdb->preparedQuery(
    'select * from test_table where test_string = ? or test_int = ?',
    ['foo', 1],
    $auto_restore_error_handling
);

var_dump($stmt->num_rows);
var_dump($stmt->affected_rows);

$better_wpdb->restoreErrorHandling();
```

❌ 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

```
❌❌❌ // NEVER EVER DO THIS. You will get hacked.

$order_by = $_GET['order'];

$better_wpdb->preparedQuery(
   'select * from test_table where test_string = ? order by ?',
   [$_GET['test_string'], $order_by]
)

✅ // Use a whitelist approach

$order_by = 'desc';
$_get_order_by = strtolower($_GET['order_by']);

if('asc' === $_get_order_by) {
    $order_by = 'asc';
}

$better_wpdb->preparedQuery(
   'select * from test_table where test_string = ? order by ?',
   [$_GET['test_string'], $order_by]
)
```

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

### Selects

[](#selects)

---

#### select

[](#select)

The most low-level select method. Returns an instance of [`mysqli_result`](https://www.php.net/manual/de/class.mysqli-result.php)

```
/** @var mysqli_result $result */
$result = $better_wpdb->select('select * from test_table where test_string = ?', ['foo']);

echo $result->num_rows

while($row = $result->fetch_array()) {
    // Do stuff with $row
}
```

---

#### selectAll

[](#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](#selectlazy) is preferred.

```
/** @var array $result */
$rows = $better_wpdb->selectAll('select * from test_table where test_string = ?', ['foo']);

foreach ($rows as $row) {
   echo $row['test_string'];
   echo $row['test_int'];
   echo $row['test_bool'];
   echo $row['test_float'];
}
```

---

#### selectLazy

[](#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](https://www.php.net/manual/en/language.generators.syntax.php) that has always only 1 row in memory.

```
❌ // you just loaded 100k rows into memory

$orders = $better_wpdb->selectAll('select * from orders where created_at selectValue('select count(*) from test_table where test_string = ?', ['foo']);

}catch (NoMatchingRowFound $e) {
    // don't catch this exception. Just a demo.
}
```

---

#### exists

[](#exists)

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

```
/** @var bool $exists */
$exists = $better_wpdb->exists('test_table', [
   'test_string' => 'foo',
   'test_float' => null,
   'test_int' => 1
   ])
```

❌ Never allow user input as keys for the array.

Inserts
-------

[](#inserts)

---

#### insert

[](#insert)

Inserts a single row into the database and returns an instance of [`mysqli_stmt`](https://www.php.net/manual/de/class.mysqli-result.php)

```
/** @var mysqli_stmt $stmt */
$stmt = $better_wpdb->insert('test_table', [
    'test_string' => 'foo',
    'test_int' => 10
]);

var_dump($stmt->affected_rows);  // (int) 1, always
var_dump($stmt->insert_id);  // (int) 10, assuming we had 9 previous records and auto-incrementing ids.
```

❌ Never allow user input as keys for the array.

---

#### bulkInsert

[](#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.

```
$result = $better_wpdb->bulkInsert('test_table', [
  ['test_string' => 'foo', 'test_float' => 10.00, 'test_int' => 1],
  ['test_string' => 'bar', 'test_float' => 20.00, 'test_int' => 2, ],
]);

var_dump($result); // (integer) 2

// This will fail since test_int can not be negative. No rows will be inserted

$result = $better_wpdb->bulkInsert('test_table', [
  ['test_string' => 'foo1', 'test_int' => 1],

  /* .. */

  ['test_string' => 'foo999', 'test_int' => 999],

  // This will throw an exception and everything will automatically roll back.
  ['test_string' => 'foo1000', 'test_int' => -1000],
]);
```

❌ 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.

```
// please don't copy-paste this code. It's just an example.

$read_csv = function() :Generator{

   $file = fopen('/path/to/hugh/csv/orders.csv')

   while(!feof($file)) {

    $row = fgetcsv($file, 4096);
    yield $row
   }
}

$importer_rows_count = $better_wpdb->bulkInsert('orders', $read_csv());

var_dump($importer_rows_count); // 100000
```

Updates
-------

[](#updates)

---

#### updateByPrimary

[](#updatebyprimary)

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

```
 /** @var int $affected_rows */
 $affected_rows = $better_wpdb->updateByPrimary('test_table', 1, [
            'test_string' => 'bar',
            'test_int' => 20,
 ]);

 // Use a custom column name
 $affected_rows = $better_wpdb->updateByPrimary('test_table', ['my_id' => 1] , [
            'test_string' => 'bar',
            'test_int' => 20,
 ]);
```

❌ Never allow user input as keys for the array.

---

#### update

[](#update)

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

```
 /** @var int $affected_rows */
 $affected_rows = $better_wpdb->update('test_table',
            ['test_int' => 10], // conditions
            ['test_bool' => true] // changes
        );
```

❌ Never allow user input as keys for the conditions

❌ Never allow user input as keys for the changes

### Deletes

[](#deletes)

---

#### delete

[](#delete)

Deletes all records that match the provided conditions.

```
 /** @var int $deleted_rows */
 $deleted_rows = $better_wpdb->delete('test_table', ['test_string' => 'foo']);
```

❌ Never allow user input as keys for the conditions

### Transactions

[](#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:

```
❌ // This is awful. What happens if a customer and an order is created but creating the payment fails?

 my_plugin_create_customer();
 my_plugin_create_create();
 my_plugin_create_payment();

✅ // wrap these calls inside a database transaction

$better_wpdb->transactional(function () {

    my_plugin_create_customer();
    my_plugin_create_create();
    my_plugin_create_payment(); // If this fails, customer and order will not be created.

});
```

### Logging

[](#logging)

---

You can a second argument to the constructor of **BetterWPDB**.

Implement the simple [QueryLogger](https://github.com/snicco/snicco/blob/master/src/Snicco/Component/better-wpdb/src/QueryLogger.php)interface and start logging your database queries to your favorite profiling service.

The following is pseudocode to log to **New Relic**:

```