Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Error in DB::query() parameter compilation with null parameter?
  • chrisvchrisv
    I think I have found an error in the query compilation when substituting parameters (FuelPHP 1.7.1).

    If I have e.g.
        $sql = 'select * from MyTable where MyField = :my-param';
        $params = array('my-param' => null);
        $rs = DB::query($sql)->parameters($params)->execute();

    The compiled SQL is 'Select * From MyTable Where MyField = null' instead of 'Select * From MyTable Where MyField is null'.

    I've been able to kludge this by means of a horrible hack in core/classes/database/query.php, but I'm sure I've not done it right!
  • HarroHarro
    Accepted Answer
    Isn't that logical?

    param() and parameters() are just about variable injection. There is no logic in there to replace the equal sign you have coded in your SQL yourself.

    It would be a better to use:

    $param = null;
    $rs = DB::select()->from('MyTable')->where('MyField', $param)->execute();

    which will generate the correct SQL, no matter what the value of $param is. (as long it is possible to generate something, obviously $param can't be an object for example).
  • chrisvchrisv
    Thanks, Harro - I can accept that (though I think there's some inconsistency: am I right in thinking that where('MyField', '=', $param) would handle nulls correctly?).

    I was going to suggest a quick mention in the documentation, but the only place I can find where parameters() is documented is a passing reference in the 'query binding' section of 'database usage'.

    Thanks, Chris
  • HarroHarro
    Yes, you are right.

    where('MyField', $param) is a shortcut or alias of where('MyField', '=', $param).

    The current DBAL was taken from the Kohana v2 codebase, is over 3 years old, and is at some points difficult to improve.

    For Fuel v2 we have written a new DBAL, which is fully PDO based, and fixes a lot of these issues. It also has platform specific drivers, so no issues with other DB's than MySQL. It is correctly production ready.

    If you have no requirements for ORM or Model_Crud, you can use the v2 DBAL in your current v1 application today. Just include 'fuelphp/database" in your composer.json.

    There are no v2 docs yet, you if you check out the tests and the index.php in https://github.com/fuelphp/database, you should be able to figure out how it works.
Add a Comment

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership

Categories

In this Discussion