DB

Resonant Core DB Class

You can read the source code for this class to stay up-to-date on any new features or enhancements we add in the future. Like every other class in our libraries, we only support PHP 5.6.x

Motivation

Parametrized queries are a wonderful security feature, but we've found that refactoring legacy (PHP4 era) code to use PDO tends to be unclean. For example:

mysql_connect(/* stuff */);
mysql_select_db(/* stuff */);

$sql = mysql_query("SELECT id, name, extra FROM table WHERE col = '" . mysql_real_esape_string($tainted) . "'");
while($r = mysql_fetch_assoc($sql)) {
    echo $r['id'] . "\t" . $r['name'] . "\t" . $r['extra'] . "\n";
}

When we convert this to PDO, the code becomes a bit messier. To wit:

$db = new PDO(/* stuff */);

$stmt = $dbh->prepare("SELECT id, name, extra FROM table WHERE col = ?");
if ($stmt->execute([$tainted])) {
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) {
        echo $r['id'] . "\t" . $r['name'] . "\t" . $r['extra'] . "\n";
    }
}

We at Resonant Core believe in building secure-by-default APIs that empower developers to write less and do more. Cleaner code is easier to audit, after all. Our DB class extends the features of PDO but offers new methods to greatly simplify workflows. In our library, the same code can be written as follows:

use \Resonantcore\Lib as Resonant;
$db = new Resonant\DB(/* */);

foreach($db->q("SELECT id, name, extra FROM table WHERE col = ?", $tainted) as $r) {
    echo $r['id'] . "\t" . $r['name'] . "\t" . $r['extra'] . "\n";
}

Alternatively (slightly more verbose, functionally equivalent):

use \Resonantcore\Lib as Resonant;
$db = new Resonant\DB(/* */);

foreach($db->dbQuery("SELECT id, name, extra FROM table WHERE col = ?", [
        $tainted
    ]) as $r) {
    echo $r['id'] . "\t" . $r['name'] . "\t" . $r['extra'] . "\n";
}

Methods

col()

Variadic alias for $this->single($statement, $params).

Parameters for col()

• $statement (string) - statement to prepare for execution
• ...$params (mixed, optional) - PHP will squash these into an array for you in PHP 5.6

Returns a string upon success, false on failure.

Usage Example for col()

if ($db->col("SELECT count(*) FROM table WHERE something = ?", $_GET['something']) > 0) {
    // Something something complete
}

dbQuery()

Perform a parametrized query by using prepared statements.

Parameters for dbQuery()

• $statement (string) - statement to prepare for execution
• $params (array, optional) - parameters to pass to prepared statement
• $fetch_style (int, optional) - PDO constant

Return a 2D array ([ row, row, row ]) on success, false on failure

Usage Example for dbQuery()

foreach($db->dbQuery("SELECT * FROM table WHERE col_a > ? AND col_b LIKE ?", [
    16,
    '%abc%'
]) as $row) {
    var_dump($row);
}

insert()

Generate and execute a parametrized INSERT INTO query following the SQL standard to insert a single record.

Parameters for insert()

• $table (string) - table name
• $map (array) - a 1D array containing the columns (and their desired values) to insert

Usage Example for insert()

$db->insert('funny_stuff', [
    'url' => 'https://www.youtube.com/watch?v=uty2zd7qizA'
    'title' => 'Do You Want to Build a Meth Lab?',
    'author' => 'Animeme',
    'tags' => 'parody, Breaking Bad, Disney, Frozen, music video, animation, comedy'
]);

iterate()

Iterate through every row in a table, executing a callback for each row.

Parameters for iterate()

• $table (string) - Name of the table to operate on
• $func (function) - Execute this on every row
• $sortby (string, optional) - Which column to sort by
• $descending (boolean, optional) - Should we sort up or down?

Usage Example for iterate()

function do_task($row)
{
    /* Magic happens! */
}

$db->iterate('pending_tasks', 'do_task');

q()

Variadic shorthand for $this->dbQuery().

Parameters for q()

• $statement (string) - statement to prepare for execution
• $params (mixed) - parameters to pass to prepared statement

Return a 2D array ([ row, row, row ]) on success, false on failure.

Usage Example for q()

foreach($db->q("SELECT * FROM table WHERE col_a > ? AND col_b LIKE ?", 16, '%abc%') as $row) {
    var_dump($row);
}

row()

Use this when you only want a single array with a single row rather than an 2D array of rows.

Parameters for row()

• $statement (string) - statement to prepare for execution
• ...$params (mixed) - parameters to pass to prepared statement

Return a 1D array ([ column, column ]) on success, false on failure.

Usage Example for row()

if (!empty($_SESSION['userid'])) {
    $userData = $db->row("SELECT username, email FROM user_data WHERE userid = ?", $_SESSION['userid']);
    echo $userData['username'];
}

sanitize()

Manually escape data for insertion into an SQL query without appending/prepending apostrophes ('). NOT RECOMMENDED FOR MOST USE CASES! Use $this->quote() instead if you need to escape a string.

Parameters for sanitize()

• $string (string) - string to escape

Returns a string.

Usage Example for sanitize()

echo $db->sanitize("The Emperor's Secret Formula for Star Wars Dialog");
// The Emperor''s Secret Formula for Star Wars Dialog

single()

Return a single column from a PDO query.

Parameters for single()

• $statement (string) - statement to prepare for execution
• $params (array, optional) - parameters to pass to prepared statement

Returns a string upon success, false upon failure.

Usage Example for single()

if ($db->single("SELECT count(*) FROM table WHERE something = ?", ['something darkside']) > 0) {
    // Something something complete
}

update()

Generate and run an UPDATE query according to the SQL standard.

Parameters for update()

• $table (string) - table name
• $changes (array) - columns to change ['column_name' => 'desired_value']
• $conditions (array) - conditions for the WHERE clause

Usage Example for update()

$db->update(
    'foo',
    ['something' => 'something darkside'],
    ['id' => 16]
);