2

I'm currently learning php and I'm using PDO to handle my database. While trying to use LIMIT in a query i encountered a problem:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''0', '25'' at line 1

After some 'research' the solution was to add this attribute to my PDO:

PDO::ATTR_EMULATE_PREPARES => false

Now my script looks like this(and the LIMIT works):

try {

    $attr = [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => false
    ];
    $pdo = new PDO('mysql:host=127.0.0.1;dbname=db_name', 'root', '', $attr);

} catch (PDOException $e) {

    exit($e->getMessage());

}

$rows = $pdo->prepare("SELECT * FROM table_name LIMIT ?, ?");
$rows->execute([0, 25]);

But my question is what does PDO::ATTR_EMULATE_PREPARES => false actually do? And is it a good solution? Does it affect in any way shape or form the level of security of that prepared statement? Is there another solution that might do a better job when it comes to using LIMIT within a prepared statement?

Thank you! :D

Alive to die - Anant
  • 70,531
  • 10
  • 51
  • 98
emma
  • 761
  • 5
  • 20
  • emma please check the answers of this thread.I hope you will get answer:-[PDO MySQL: Use PDO::ATTR_EMULATE_PREPARES or not?](https://stackoverflow.com/questions/10113562/pdo-mysql-use-pdoattr-emulate-prepares-or-not) – Alive to die - Anant Sep 24 '18 at 05:43
  • Hey @AlivetoDie, i saw that question but i can't figure out what EMULATE_PREPARES actually does from it :( – emma Sep 24 '18 at 05:45
  • 1
    PDO::ATTR_EMULATE_PREPARES Enables or disables emulation of prepared statements. Some drivers do not support native prepared statements or have limited support for them. Use this setting to force PDO to either always emulate prepared statements (if TRUE and emulated prepares are supported by the driver), or to try to use native prepared statements (if FALSE). It will always fall back to emulating the prepared statement if the driver cannot successfully prepare the current query. Requires bool. Check here:- [PDO::setAttribute](http://php.net/manual/en/pdo.setattribute.php) – Alive to die - Anant Sep 24 '18 at 05:48
  • Hey @AlivetoDie,first of all thank you for taking time to help me :D. I saw that on php manual too but what does "emulating" a prepare statement means? :-s i don't get that X_X. If it is on off it will affect the security of my prepare statement? :-s Thank youuu! :D – emma Sep 24 '18 at 05:54
  • check this answer:- https://stackoverflow.com/a/8298402/4248328 . it will make things clear out to you – Alive to die - Anant Sep 24 '18 at 05:56
  • 1
    @AlivetoDie, thank you:D - one last question: so basically when i turn off(false) the EMULATE_PREPARES i'm actually starting to use a real prepared statement which basically means better security, is that conclusion right? :-s – emma Sep 24 '18 at 06:06
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/180653/discussion-between-emma-and-alive-to-die). – emma Sep 24 '18 at 09:17

0 Answers0