2

TL;DR: To access DB functionality in my codebase I currently use a custom layer throughout the code. The layer needs an upgrade in order to begin accepting prepared statements, which it currently does not. I have a choice between abandoning using the custom layer in favor of using another layer directly instead - such as PDO/mysqli, or to keep using my layer as is, but amend it to accept prepared statements (as much as it will allow via making any necessary adjustments to the constructs encapsulated inside the layer itself (in my case using myslqi/PDO behind my layer, to avoid having to rewrite every single call to the layer throughout my code)


I currently have mysqli extensions in my PHP codebase. Also, in my codebase I do not use prepared statements. My calls to DB are wrapped into proprietary method calls that are really just redirects to mysqli. Something like this:

/*
 * Thousands of calls like this in my codebase:
 */
db_query("SELECT * FROM table WHERE id = " . db_input($id));

/*
 * Single file with these functions:
 */
function db_query($query)
{
     global $dblink;
     return mysqli_query($dblink, $query);
}

function db_input($param)
{
    //various checks on input, if/else (redacted)
    global $dblink;
    $param = mysqli_real_escape_string($dblink, $param);
    return $param;
}

I am considering switching to using PDO, because PDO has named parameters & prepared statements and I like those. (mysqli also has prepared statements but not named parameters and I'm partial to that). How I will convert is another question, but my concern is ... Let's say I successfully convert my codebase into using PDO. What are my long term consequences?

Option 1

I can continue to use my own proprietary methods like db_query that now use PDO inside of them, like this:

function db_query($query)
{
     return $db->query($query, PDO::FETCH_ASSOC);
}

This will keep my code intact, and keep my actual DB native PDO methods in a single file

Option 2

I can start to use PDO directly from my code, in all its glory, something like

$sql = 'SELECT * FROM table WHERE id = :id';
$sth = $dbh->prepare($sql, ... );
$sth->execute(array(':id' => 1));
$record = $sth->fetchAll();

I will no longer use db_query.

This will make my code peppered in PDO statements everywhere and much harder to rewrite should I want to switch away from PDO later

Goal/Spirit of the question: I am interested in long-term effects, side-effects, maintenance of the codebase. I am essentially asking .. do I keep wrapping PDO/mysqli into my proprietary db_query calls, or do I start using PDO/mysqli directly in my code?

To fully use PDO prepared statements and full power of PDO, it seems I best use Option 2, but that means PDO will be "out of the bag of confinement of my proprietary redirect calls (db_query())." and PDO methods will spill out into my codebase and no longer be confined into one single "DB methods" file. I am concerned that doing so will become an inconvenience later on in my code should I for example decide that I want to go back to myqsli or switch to Doctrine or what have you.

My alternative is to keep using db_query but beef it up to allow for named parameters, to essentially write my own wrapper over PDO. (this will be Option 1 + provisions to write a wrapper over PDO so I can keep using db_query() + named parameters + PDO behind the scenes). But will I open myself up to a world of trouble (extra work, little gain, bugs inherent to writing my own wrapper ). Although this options means I will have my own proprietary layer that will in theory stay intact, while I can change mysqli to PDO, and back, behind the scenes.

Suggestion by Robert

Option 1 + amend the wrapper to accept parameters

//to call:
db_query(
    'SELECT * FROM table WHERE id = :id', 
    array(':id' => 1)
);

//update to accept params
function db_query($sql, $params = null)
{
    $sth = $dbh->prepare($sql, ... );
    $sth->execute($params);
    $record = $sth->fetchAll();
}
  • Why don't you convert your existing wrappers to PDO, and then use raw PDO in the application for those few cases where you require the additional power? – Robert Harvey Jun 10 '16 at 15:13
  • so say I convert my file that houses mysqli native calls, to PDO calls and keep using db_query() + db_input() for most cases. My plight is that most of my SQL statements use some kind of a parameter and I wanted to get away from using non-prepared statements like I have been using. I guess it begs another question: is my using db_query() + db_input() (aka non-prepared statement), just as good as using prepared statement + parameters that are offered by PDO/mysqli – Dennis Jun 10 '16 at 15:13
  • 1
    You can't make the inner part of your wrappers use prepared statements? What about a generic array parameter; does PHP support that? – Robert Harvey Jun 10 '16 at 15:15
  • I probably can. That'd be Option 1 + update my wrapper to accept parameters. Then just pass them to PDO – Dennis Jun 10 '16 at 15:20
  • 1
    Yes, that's exactly what I had in mind. – Robert Harvey Jun 10 '16 at 15:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.