PHPackages                             paragonie/easydb - 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. paragonie/easydb

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

paragonie/easydb
================

Easy-to-use database abstraction

v3.1.1(7mo ago)744273.4k↓16.1%89[1 issues](https://github.com/paragonie/easydb/issues)20MITPHPPHP ^8CI passing

Since May 23Pushed 1mo ago28 watchersCompare

[ Source](https://github.com/paragonie/easydb)[ Packagist](https://packagist.org/packages/paragonie/easydb)[ RSS](/packages/paragonie-easydb/feed)WikiDiscussions master Synced 1mo ago

READMEChangelog (10)Dependencies (5)Versions (49)Used By (20)

EasyDB - Simple Database Abstraction Layer
==========================================

[](#easydb---simple-database-abstraction-layer)

[![Build Status](https://github.com/paragonie/easydb/actions/workflows/ci.yml/badge.svg)](https://github.com/paragonie/easydb/actions)[![Static Analysis](https://github.com/paragonie/easydb/actions/workflows/psalm.yml/badge.svg)](https://github.com/paragonie/easydb/actions)[![Driver Integration Tests](https://github.com/paragonie/easydb/actions/workflows/integration.yml/badge.svg)](https://github.com/paragonie/easydb/actions)[![Latest Stable Version](https://camo.githubusercontent.com/874f5543cc0ef90e3cc6f533dd5a1346d3abbef86f11cb371cf878e4e796f709/68747470733a2f2f706f7365722e707567782e6f72672f70617261676f6e69652f6561737964622f762f737461626c65)](https://packagist.org/packages/paragonie/easydb)[![Latest Unstable Version](https://camo.githubusercontent.com/459592f55c19d7ac7594cc1272d03fa73f0052ec24223e3373f1e9db83b5ef82/68747470733a2f2f706f7365722e707567782e6f72672f70617261676f6e69652f6561737964622f762f756e737461626c65)](https://packagist.org/packages/paragonie/easydb)[![License](https://camo.githubusercontent.com/3e8ca94e318af3d0b57f631b9e0720b69fda9a5e5136d225a5ba8c5c1c103a12/68747470733a2f2f706f7365722e707567782e6f72672f70617261676f6e69652f6561737964622f6c6963656e7365)](https://packagist.org/packages/paragonie/easydb)[![Downloads](https://camo.githubusercontent.com/3f2acd1e98d3dee2feb0c2875f403a4f8f3ace63fb0be48055b726975abd3cad/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f64742f70617261676f6e69652f6561737964622e737667)](https://packagist.org/packages/paragonie/easydb)

PDO lacks brevity and simplicity; EasyDB makes separating data from instructions easy (and aesthetically pleasing).

EasyDB was created by [Paragon Initiative Enterprises](https://paragonie.com)as part of our effort to encourage better [application security](https://paragonie.com/service/appsec) practices.

Check out our other [open source projects](https://paragonie.com/projects) too.

If you're looking for a full-fledged query builder, check out [Latitude](https://github.com/shadowhand/latitude) and [Aura.SqlQuery](https://github.com/auraphp/Aura.SqlQuery), which can be used with EasyDB.

If you'd like to use EasyDB but cache prepared statements in memory for multiple queries (i.e. to reduce database round-trips), check out our [EasyDB-Cache](https://github.com/paragonie/easydb-cache) wrapper class.

Installing EasyDB
-----------------

[](#installing-easydb)

First, [get Composer](https://getcomposer.org/download/), if you don't already use it.

Next, run the following command:

```
/path/to/your/local/composer.phar require paragonie/easydb:^3
```

If you've installed Composer in `/usr/bin`, you can replace `/path/to/your/local/composer.phar` with just `composer`.

Why Use EasyDB? Because it's cleaner!
-------------------------------------

[](#why-use-easydb-because-its-cleaner)

Let's refactor a dangerous PHP snippet that previously used string concatenation to pass user input instead of prepared statements. For example, imagine something that just dropped `{$_GET['blogpostid']}` into the middle of a `mysql_query()` statement. Let's make it secure.

### The PDO Way

[](#the-pdo-way)

```
$db = new \PDO(
    'mysql:host=localhost;dbname=something',
    'username',
    'putastrongpasswordhere'
);

$statement = $db->prepare('SELECT * FROM comments WHERE blogpostid = ? ORDER BY created ASC');
$exec = $statement->execute([$_GET['blogpostid']]);
$rows = $statement->fetchAll(\PDO::FETCH_ASSOC);
foreach ($rows as $row) {
    $template_engine->render('comment', $row);
}
```

That's a little wordy for such a simple task. If we do this in multiple places, we end up repeating ourselves a lot.

### The EasyDB Solution

[](#the-easydb-solution)

```
$db = \ParagonIE\EasyDB\Factory::fromArray([
    'mysql:host=localhost;dbname=something',
    'username',
    'putastrongpasswordhere'
]);

$rows = $db->run('SELECT * FROM comments WHERE blogpostid = ? ORDER BY created ASC', $_GET['blogpostid']);
foreach ($rows as $row) {
    $template_engine->render('comment', $row);
}
```

We made it a one-liner.

What else can EasyDB do quickly?
--------------------------------

[](#what-else-can-easydb-do-quickly)

### Insert a row into a database table

[](#insert-a-row-into-a-database-table)

```
$db->insert('comments', [
    'blogpostid' => $_POST['blogpost'],
    'userid' => $_SESSION['user'],
    'comment' => $_POST['body'],
    'parent' => isset($_POST['replyTo']) ? $_POST['replyTo'] : null
]);
```

This is equivalent to the following SQL query (assuming `$_POST['blogpostid']`is equal to `123`, `$_SESSION['user']` is equal to `234`, `$_POST['body']` is equal to `test`, and `$_POST['replyTo']` is equal to `3456`):

```
INSERT INTO comments (blogpostid, userid, comment, parent) VALUES (
    123,
    234,
    'test',
    3456
);
```

#### Insert a row and return the new record ID

[](#insert-a-row-and-return-the-new-record-id)

Most of the time, you can get away with this:

```
$newRecordId = $db->insertReturnId($table, $fields);
```

However, PostgreSQL requires you to specify the sequence name.

```
$newRecordId = $db->insertReturnId($table, $fields, $sequeence);
```

#### Build an insert without executing

[](#build-an-insert-without-executing)

```
$sql = $db->buildInsertQuery('comments', [
    'blogpostid',
    'userid',
    'comment'
]);

// INSERT INTO comments (blogpostid, userid, comment) VALUES (?, ?, ?)

$result = $db->q(
    $sql,
    $values,
    \PDO::FETCH_BOTH,
    true
);
```

### Update a row from a database table

[](#update-a-row-from-a-database-table)

```
$db->update('comments', [
    'column' => 'foo',
    'otherColumn' => 123456,
    'approved' => true
], [
    'commentid' => $_POST['comment']
]);
```

This is equivalent to the following SQL query (assuming `$_POST['comment']` is equal to `789`):

```
UPDATE comments
SET
  column = 'foo',
  otherColumn = 123456,
  approved = TRUE
WHERE commentid = 789
```

### Delete a row from a database table

[](#delete-a-row-from-a-database-table)

```
// Delete all of this user's comments
$db->delete('comments', [
    'userid' => 3
]);
```

This is equivalent to the following SQL query:

```
DELETE FROM comments WHERE userid = 3
```

### Fetch a single row from a table

[](#fetch-a-single-row-from-a-table)

```
$userData = $db->row(
    "SELECT * FROM users WHERE userid = ?",
    $_GET['userid']
);
```

Note: This expects a variadic list of arguments, not an array. If you have multiple parameters, stack them like this:

```
$userData = $db->row(
    "SELECT * FROM users WHERE userid = ? AND other = ?",
    $_GET['userid'],
    $_GET['other']
);
```

This is **wrong**:

```
$userData = $db->row(
    "SELECT * FROM users WHERE userid = ? AND other = ?",
    array($userid, $other) // WRONG, should not be in an array
);
```

### Fetch a single column from a single row from a table

[](#fetch-a-single-column-from-a-single-row-from-a-table)

```
$exists = $db->cell(
    "SELECT count(id) FROM users WHERE email = ?",
    $_POST['email']
);

/* OR YOU CAN CALL IT THIS WAY: */
$exists = $db->single(
    "SELECT count(id) FROM users WHERE email = ?",
    array(
        $_POST['email']
    )
);
```

Note: `cell()` expects a variadic list of arguments, not an array. If you have multiple parameters, stack them like this:

```
$exists = $db->cell(
    "SELECT count(id) FROM users WHERE email = ? AND username = ?",
    $_POST['email'],
    $_POST['usenrame']
);
```

This is **wrong**:

```
$exists = $db->cell(
    "SELECT count(id) FROM users WHERE email = ? AND username = ?",
    array($email, $username) // WRONG, should not be in an array
);
```

Alternatively, you can use `single()` instead of `cell()` if you really want to pass an array.

### Try to perform a transaction

[](#try-to-perform-a-transaction)

```
$save = function (EasyDB $db) use ($userData, $query) : int {
    $db->safeQuery($query, [$userData['userId']]);
    return \Some\Other\Package::CleanUpTable($db);
};
// auto starts, commits and rolls back a transaction as necessary
$returnedInt = $db->tryFlatTransaction($save);
```

### Generate dynamic query conditions

[](#generate-dynamic-query-conditions)

```
$statement = EasyStatement::open()
    ->with('last_login IS NOT NULL');

if (strpos($_POST['search'], '@') !== false) {
    // Perform a username search
    $statement->orWith('username LIKE ?', '%' . $db->escapeLikeValue($_POST['search']) . '%');
} else {
    // Perform an email search
    $statement->orWith('email = ?', $_POST['search']);
}

// The statement can compile itself to a string with placeholders:
echo $statement; /* last_login IS NOT NULL OR username LIKE ? */

// All the values passed to the statement are captured and can be used for querying:
$user = $db->single("SELECT * FROM users WHERE $statement", $statement->values());
```

***Note**: Passing values with conditions is entirely optional but recommended.*

#### Variable number of "IN" arguments

[](#variable-number-of-in-arguments)

```
// Statements also handle translation for IN conditions with variable arguments,
// using a special ?* placeholder:
$roles = [1];
if ($_GET['with_managers']) {
    $roles[] = 2;
}

$statement = EasyStatement::open()->in('role IN (?*)', $roles);

// The ?* placeholder is replaced by the correct number of ? placeholders:
echo $statement; /* role IN (?, ?) */

// And the values will be unpacked accordingly:
print_r($statement->values()); /* [1, 2] */
```

#### Grouping of conditions

[](#grouping-of-conditions)

```
// Statements can also be grouped when necessary:
$statement = EasyStatement::open()
    ->group()
        ->with('subtotal > ?')
        ->andWith('taxes > ?')
    ->end()
    ->orGroup()
        ->with('cost > ?')
        ->andWith('cancelled = 1')
    ->end();

echo $statement; /* (subtotal > ? AND taxes > ?) OR (cost > ? AND cancelled = 1) */
```

### Insert and Update with custom placeholder

[](#insert-and-update-with-custom-placeholder)

Since Version 2.12.0, EasyDB supports placeholders for calling stored procedures and SQL functions when inserting or updating data.

The `EasyPlaceholder` class is constructed in the same fashion as other EasyDB methods: The first argument, the "mask", must be a string. The mask may contain `?` placeholders, and any subsequent arguments will fill in for the `?` placeholders when the query is executed.

```
$db->insert('user_auth', [
    'user_id' => 1,
    'timestamp' => new EasyPlaceholder('NOW()'),
    'expired' => new EasyPlaceholder('TIMESTAMPADD(HOUR, 2, NOW())'),
    'location' => new EasyPlaceholder(
        "ST_GeomFromText(CONCAT('POINT(', ?, ' ', ?, ')'))",
        50.4019514,
        30.3926105
    )
]);

$db->update(
    'user_auth',
    [
        'last_update' => new EasyPlaceholder('NOW()'),
    ],
    [
        'user_id' => 1
    ]
);
```

> Security warning: Do not concatenate user input into the first parameter.

`EasyPlaceholder` can be used in `insert()`, `insertIgnore()`, `insertOnDuplicateKeyUpdate()`, and `update()`.

What if I need PDO for something specific?
------------------------------------------

[](#what-if-i-need-pdo-for-something-specific)

```
$pdo = $db->getPdo();
```

Can I create an EasyDB wrapper for an existing PDO instance?
------------------------------------------------------------

[](#can-i-create-an-easydb-wrapper-for-an-existing-pdo-instance)

**Yes!** It's as simple as doing this:

```
$easy = new \ParagonIE\EasyDB\EasyDB($pdo, 'mysql');
```

How do I run tests ?
--------------------

[](#how-do-i-run-tests-)

```
vendor/bin/phpunit
```

Using Psalm's Security Analysis with EasyDB
-------------------------------------------

[](#using-psalms-security-analysis-with-easydb)

First, make sure you've [read the Psalm documentation](https://psalm.dev/docs/security_analysis/).

EasyDB's API exposes several taint sinks. Next, run the following command on your codebase that uses EasyDB to identify sources of SQL injection risk.

```
vendor/bin/psalm --taint-analysis

```

This will expose where you're passing tainted data to EasyDB in a potentially unsafe way.

Troubleshooting Common Issues
-----------------------------

[](#troubleshooting-common-issues)

### Only one-dimensional arrays are allowed

[](#only-one-dimensional-arrays-are-allowed)

This comes up a lot when trying to pass an array of parameters to `run()`.

`EasyDB::run()` expects a query string, then any number of optional parameters. It does **NOT** expect an array of all the parameters.

If you want to use an API that looks like `$obj->method($string, $array)`, use `safeQuery()` instead of `run()`.

```