CODEWITHSUNDEEP
phpmysqllaravelpdo
Abdunnasir K P
Abdunnasir K P

Reputation: 117

SQLSTATE[HY093]: Invalid parameter number and PDO::ATTR_EMULATE_PREPARES

When executing a query as shown below in laravel

  $result = DB::select(
                'SELECT p.f1 FROM `tab1` m
                INNER JOIN `tab2` p
                ON m.id= p.fk
                WHERE m.good_id = :good_id AND p.good_id = :good_id LIMIT 1',
                ['good_id' => 12]
            );

Got an error SQLSTATE[HY093]: Invalid parameter number.

Found a fix for that.

We can add the below code

'options' => [
     PDO::ATTR_EMULATE_PREPARES => true,
],

to the mysql array of config/database.php.

But using that will have any issue from the security point of view? https://github.com/Microsoft/msphpsql/issues/46

Issue mentioned in the above post is still open?

Upvotes: 1

Views: 233

Answers (1)

Cemal
Cemal

Reputation: 1579

Yes, that security issue applies to you as well. however there is another quick fix for your problem which doesn't involve PDO::ATTR_EMULATE_PREPARES

$result = DB::select(
    'SELECT p.f1 FROM `tab1` m
    INNER JOIN `tab2` p
    ON m.id= p.fk
    WHERE m.good_id = :good_id AND p.good_id = :good_id0 LIMIT 1',
            ['good_id' => 12, 'good_id0' => 12]
    );

which is increasing the number of identifiers in your sql, and putting the same values in there. Also you can optimize your sql,

$result = DB::select(
    'SELECT p.f1 FROM `tab1` m
    INNER JOIN `tab2` p
    ON (m.id= p.fk AND m.good_id = p.good_id)
    WHERE m.good_id = :good_id LIMIT 1',
            ['good_id' => 12]
    );

this will enable your sql to require only 1 identifier.

Upvotes: 2

Related Questions