CODEWITHSUNDEEP
sqllaravelprepared-statementeloquentinfinidb
Kevin Pei
Kevin Pei

Reputation: 5872

Don't use prepared statements in Laravel Eloquent ORM?

Can I have Eloquent ORM run a query without using prepared statements? Or do I have to use whereRaw()?

I need to use a raw query because I'm trying to interact with InfiniDB, which lacks support for prepared statements from PHP. At any rate, all queries will be using internally generated data, not user input so it should not be a security issue.

Upvotes: 5

Views: 5842

Answers (2)

toraman
toraman

Reputation: 604

There's an easy way to do it. In the file config/database.php you can specify options for php PDO like so:

'mysql_unprepared' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PROXY_PORT', '6033'),
        'username' => env('DB_CACHED_USERNAME', 'forge'),
        'password' => env('DB_CACHED_PASSWORD', ''),
        'database' => env('DB_DATABASE', 'forge'),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'prefix_indexes' => true,
        'strict' => true,
        'engine' => null,
        'options' => extension_loaded('pdo_mysql') ? [
            PDO::ATTR_EMULATE_PREPARES => true,
        ] : [],
        'modes'  => [
            'ONLY_FULL_GROUP_BY',
            'STRICT_TRANS_TABLES',
            'NO_ZERO_IN_DATE',
            'NO_ZERO_DATE',
            'ERROR_FOR_DIVISION_BY_ZERO',
            'NO_ENGINE_SUBSTITUTION',
        ],
    ],

As you can see, there is an option PDO::ATTR_EMULATE_PREPARES which, when set to true, will do a prepare-like action on application level and send the query unprepared instead. I didn't figure PDO had this option until I had already created an extension for Laravel's mysql driver just to intercept select queries and do unprepared mysqli queries instead so that ProxySql could cache them.

So this answer could have been a lot more complicated. Cheers.

Upvotes: 1

peterm
peterm

Reputation: 92785

For anything other than SELECT you can use unprepared()

DB::unprepared($sql);

For an unprepared SELECT you can use plain PDO query() by getting access to active PDO connection through getPdo()

$pdo = DB::getPdo();
$query = $pdo->query($sql);
$result = $query->fetchAll();

Upvotes: 1

Related Questions