5

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.

Kevin Pei
  • 5,800
  • 7
  • 38
  • 55

2 Answers2

1

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();
peterm
  • 91,357
  • 15
  • 148
  • 157
  • So essentially there's no easy way to do what I need to do - I'll just have to manually write out the query? – Kevin Pei Aug 14 '14 at 03:09
  • @peterm Maybe you can help me. Look at this : https://stackoverflow.com/questions/51838922/how-can-i-convert-many-statement-mysql-to-laravel-eloquent – moses toh Aug 14 '18 at 10:29
1

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.

toraman
  • 598
  • 4
  • 14