PHP Classes

EasyDB: Simple Database Abstraction Layer around PDO

Recommend this page to a friend!
  Info   Documentation   View files Files   Install with Composer Install with Composer   Download Download   Reputation   Support forum   Blog    
Ratings Unique User Downloads Download Rankings
StarStarStarStar 67%Total: 715 All time: 4,626 This week: 36Up
Version License PHP version Categories
paragonie-easydb 0.2.48MIT/X Consortium ...5.6.0PHP 5, Databases
Description 

Authors

Paragon Initiative Enterprises
Woody Gilk
SignpostMarv


Contributor

This class is a simple Database Abstraction Layer around PDO.

It wraps around PDO to access databases and execute queries providing functions that execute common queries and database operations. Currently it can:

- Execute a prepared query using given parameters and return all results in an array
- Execute a query to retrieve a single value or a values of a record using a variable number of parameters
- Delete table records that match a given condition
- Insert one or more table records given an array of field values
- Update a table record that matches a condition using given field values
- Manage transactions

It can also return a PDO object so you can execute other operations calling that object directly.

Picture of Scott Arciszewski
  Performance   Level  
Name: Scott Arciszewski <contact>
Classes: 37 packages by
Country: United States United States
Age: ???
All time rank: 1173170 in United States United States
Week rank: 22 Up4 in United States United States Up
Innovation award
Innovation award
Nominee: 28x

Winner: 1x

Documentation

EasyDB - Simple Database Abstraction Layer

Build Status Latest Stable Version Latest Unstable Version License Downloads

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

EasyDB was created by Paragon Initiative Enterprises as part of our effort to encourage better application security practices.

Check out our other open source projects too.

If you're looking for a full-fledged query builder, check out Latitude and 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 wrapper class.

Installing EasyDB

First, get Composer, 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!

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

$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

$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?

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
);

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

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

$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

$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

$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

$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

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

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

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?

$pdo = $db->getPdo();

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 ?

vendor/bin/phpunit

Using Psalm's Security Analysis with EasyDB

First, make sure you've read the Psalm documentation.

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

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

<?php
/
 * @var EasyDB $db
 * @var string $query
 * @var array $params 
 */
- $rows = $db->run($query, $params);
+ $rows = $db->safeQuery($query, $params);

Alternatively, you can flatten your array with the splat operator:

<?php
/
 * @var EasyDB $db
 * @var string $query
 * @var array $params 
 */
- $rows = $db->run($query, $params);
+ $rows = $db->run($query, ...$params);

EasyDB's run() method is a variadic wrapper for safeQuery(), so either solution is correct.

Support Contracts

If your company uses this library in their products or services, you may be interested in purchasing a support contract from Paragon Initiative Enterprises.


  Files folder image Files (64)  
File Role Description
Files folder image.github (1 directory)
Files folder imagesrc (4 files, 1 directory)
Files folder imagetests (40 files)
Accessible without login Plain text file .php_cs Example Example script
Accessible without login Plain text file CHANGELOG.md Data Auxiliary data
Accessible without login Plain text file composer.json Data Auxiliary data
Accessible without login Plain text file LICENSE Lic. License
Accessible without login Plain text file phpunit.xml.dist Data Auxiliary data
Accessible without login Plain text file psalm.baseline.xml Data Auxiliary data
Accessible without login Plain text file psalm.xml Data Auxiliary data
Accessible without login Plain text file README.md Doc. Documentation

  Files folder image Files (64)  /  .github  
File Role Description
Files folder imageworkflows (2 files)

  Files folder image Files (64)  /  .github  /  workflows  
File Role Description
  Accessible without login Plain text file ci.yml Data Auxiliary data
  Accessible without login Plain text file psalm.yml Data Auxiliary data

  Files folder image Files (64)  /  src  
File Role Description
Files folder imageException (10 files)
  Accessible without login Plain text file EasyDB.php Class Class source
  Accessible without login Plain text file EasyPlaceholder.php Class Class source
  Accessible without login Plain text file EasyStatement.php Class Class source
  Accessible without login Plain text file Factory.php Class Class source

  Files folder image Files (64)  /  src  /  Exception  
File Role Description
  Accessible without login Plain text file ConstructorFailed.php Class Exception
  Accessible without login Plain text file EasyDBException.php Class Class source
  Accessible without login Plain text file ExceptionInterface.php Class Class source
  Accessible without login Plain text file InvalidIdentifier.php Class Exception
  Accessible without login Plain text file InvalidTableName.php Class Class source
  Accessible without login Plain text file MustBeArrayOrEasyStatement.php Class Class source
  Accessible without login Plain text file MustBeEmpty.php Class Class source
  Accessible without login Plain text file MustBeNonEmpty.php Class Class source
  Accessible without login Plain text file MustBeOneDimensionalArray.php Class Class source
  Accessible without login Plain text file QueryError.php Class Exception

  Files folder image Files (64)  /  tests  
File Role Description
  Accessible without login Plain text file CellTest.php Class Class source
  Accessible without login Plain text file ColTest.php Class Class source
  Accessible without login Plain text file ColumnTest.php Class Class source
  Accessible without login Plain text file ConstructorFailedTest.php Class Class source
  Accessible without login Plain text file CsvTest.php Class Class source
  Accessible without login Plain text file EasyDBWriteTest.php Class Class source
  Accessible without login Plain text file EasyStatementTest.php Class Class source
  Accessible without login Plain text file EmulatePreparesDisabledTest.php Class Class source
  Accessible without login Plain text file ErrorCodeTest.php Class Class source
  Accessible without login Plain text file ErrorInfoTest.php Class Class source
  Accessible without login Plain text file EscapeIdentifierTest.php Class Class source
  Accessible without login Plain text file EscapeLikeTest.php Class Class source
  Accessible without login Plain text file FactoryTest.php Class Class source
  Accessible without login Plain text file FirstTest.php Class Class source
  Accessible without login Plain text file GetAttributeTest.php Class Class source
  Accessible without login Plain text file GetAvailableDriversTest.php Class Class source
  Accessible without login Plain text file GetDriverTest.php Class Class source
  Accessible without login Plain text file InsertGetTest.php Class Class source
  Accessible without login Plain text file InsertManyFlatTransactionTest.php Class Class source
  Accessible without login Plain text file InsertManyFlatTran...untimeException.php Class Class source
  Accessible without login Plain text file InsertManyTest.php Class Class source
  Accessible without login Plain text file InsertReturnIdTest.php Class Class source
  Accessible without login Plain text file InsertTest.php Class Class source
  Accessible without login Plain text file InTransactionTest.php Class Class source
  Accessible without login Plain text file Is1DArrayTest.php Class Class source
  Accessible without login Plain text file Is1DArrayTestThenEscapeValueSetTest.php Class Class source
  Accessible without login Plain text file Is1DArrayThenDeleteReadOnlyTest.php Class Class source
  Accessible without login Plain text file PrepareTest.php Class Class source
  Accessible without login Plain text file QTest.php Class Class source
  Accessible without login Plain text file QuoteTest.php Class Class source
  Accessible without login Plain text file QuoteThenExecTest.php Class Class source
  Accessible without login Plain text file QuoteThenExecThenLastInsertIdTest.php Class Class source
  Accessible without login Plain text file QuoteThenQueryTest.php Class Class source
  Accessible without login Plain text file RowTest.php Class Class source
  Accessible without login Plain text file RunTest.php Class Class source
  Accessible without login Plain text file SetAttributeTest.php Class Class source
  Accessible without login Plain text file SingleTest.php Class Class source
  Accessible without login Plain text file SingleTestIs1DArra...DeleteWriteTest.php Class Class source
  Accessible without login Plain text file SingleTestThenExistsTest.php Class Class source
  Accessible without login Plain text file UpdateTest.php Class Class source

The PHP Classes site has supported package installation using the Composer tool since 2013, as you may verify by reading this instructions page.
Install with Composer Install with Composer
 Version Control Unique User Downloads Download Rankings  
 100%
Total:715
This week:0
All time:4,626
This week:36Up
User Ratings User Comments (1)
 All time
Utility:95%StarStarStarStarStar
Consistency:95%StarStarStarStarStar
Documentation:100%StarStarStarStarStarStar
Examples:-
Tests:-
Videos:-
Overall:67%StarStarStarStar
Rank:469
 
good
7 years ago (muabshir)
70%StarStarStarStar