PHP Classes
elePHPant
Icontem

EasyDB: Simple Database Abstraction Layer around PDO

Recommend this page to a friend!
  Info   View files Documentation   View files View files (50)   DownloadInstall with Composer Download .zip   Reputation   Support forum (1)   Blog    
Last Updated Ratings Unique User Downloads Download Rankings
2017-05-12 (2 months ago) RSS 2.0 feedStarStarStarStar 70%Total: 400 This week: 3All time: 6,293 This week: 248Up
Version License PHP version Categories
paragonie-easydb 0.2.18MIT/X Consortium ...5.6.0PHP 5, Databases
Description Author

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.

  Performance   Level  
Name: Scott Arciszewski <contact>
Classes: 6 packages by
Country: United States United States
Age: ???
All time rank: 2222280 in United States United States
Week rank: 28 Up3 in United States United States Up
Innovation award
Innovation award
Nominee: 3x

Details

EasyDB - Simple Database Abstraction Layer

Build Status

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.

Why Use EasyDB? Because it's cleaner!

Let's refactor the following legacy insecure code snippet to prevent SQL injection.

$query = mysql_query(
    "SELECT * FROM comments WHERE blogpostid = {$_GET['blogpostid']} ORDER BY created ASC"
);
while($row = mysql_fetch_assoc($query)) {
    $template_engine->render('comment', $row);
}

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 = $exec->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::create(
    '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
]);

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', [
    'approved' => true
], [
    'commentid' => $_POST['comment']
]);

Delete a row from a database table

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

Fetch a single row from a table

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

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

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

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 ?

./phpunit.sh
  Files folder image Files  
File Role Description
Files folder imagesrc (3 files, 1 directory)
Files folder imagetests (37 files)
Accessible without login Plain text file .php_cs Example Example script
Accessible without login Plain text file .travis.yml Data Auxiliary data
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 README.md Doc. Documentation

  Files folder image Files  /  src  
File Role Description
Files folder imageException (3 files)
  Accessible without login Plain text file EasyDB.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  /  src  /  Exception  
File Role Description
  Accessible without login Plain text file ConstructorFailed.php Class Exception
  Accessible without login Plain text file InvalidIdentifier.php Class Exception
  Accessible without login Plain text file QueryError.php Class Exception

  Files folder image Files  /  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 EasyDBTest.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 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 InsertManyTest.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 SafeQueryTest.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

 Version Control Unique User Downloads Download Rankings  
 100%
Total:400
This week:3
All time:6,293
This week:248Up
User Ratings User Comments (1)
 All time
Utility:100%StarStarStarStarStarStar
Consistency:100%StarStarStarStarStarStar
Documentation:100%StarStarStarStarStarStar
Examples:-
Tests:-
Videos:-
Overall:70%StarStarStarStar
Rank:362
 
good
9 months ago (muabshir)
70%StarStarStarStar