How To Use Parameter Binding Correctly

[David Tan]

Hi All,

Can someone please enlighten me how to use parameter binding correctly with the following scenario?

redbean works fine when I simply pass php variables directly as part of the query.

$results = R::find("merchant_product", "title like '%$val%' order by title LIMIT " . ($page-1)*$limit . ",$limit");

Since it's not safe to do so (due to sql injection), hence I tried parameter binding

$results = R::find("merchant_product", "title like ? order by title LIMIT ?,?",array('%'.$val.'%',(($page-1)*$limit),$limit));

But it's failing with error message:

RedBeanPHP\RedException\SQL: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''25' -- keep-cache' at line 1 in C:\wamp\www\topbestpriceuk\compare\lib\rb.php on line 735

* note that $limit is 25 in this case

Your help appreciated, thanks!

[Matthew Frederico]

I could be wrong - just spitballing:

$results = R::find("merchant_product", "title like ? order by title LIMIT ?,?",array('%'.$val.'%',(($page-1)*$limit),$limit));

should probably be:

$results = R::find("merchant_product", "title like %?% order by title LIMIT ?,?",array($val,(($page-1)*$limit),$limit));

keeping the %'s inside the find statement instead as part of your passed in field?

--
You received this message because you are subscribed to the Google Groups "redbeanphp" group.
To unsubscribe from this group and stop receiving emails from it, send an email to redbeanorm+unsubscribe@googlegroups.com.
To post to this group, send email to redbe...@googlegroups.com.
Visit this group at https://groups.google.com/group/redbeanorm.
For more options, visit https://groups.google.com/d/optout.

--

-`;'- Matthew Frederico

[Lynesth]

This should be working juste like you did it.

What version of Redbean / PHP / MySQL are you using ?

Lyn.

[Lynesth]

Ok I found where your error comes from.

You need to make sure that $page and $limit are integers and not strings (variables that you get from html forms - $_POST or $_GET - are strings).

So you either have to make sure they are integers before you pass them as parameters for your request, or you could cast them to int on the fly in the array :

<?php
    // Cast the parameters to int from the start
    $page = (int) $_POST['page'];
    $limit = (int) $_POST['limit'];
    $results = R::find("merchant_product", "title like ? order by title LIMIT ?,?", [ '%'.$val.'%', (($page - 1) * $limit), $limit ]);

    // OR you can cast them to int directly inside the array
    $results = R::find("merchant_product", "title like ? order by title LIMIT ?,?", [ '%'.$val.'%', (int) (($page - 1) * $limit), (int) $limit ]);

    // OR you cant intval() them in the array, whichever you prefer
    $results = R::find("merchant_product", "title like ? order by title LIMIT ?,?", [ '%'.$val.'%', intval(($page - 1) * $limit), intval($limit) ]);
?>

Regards,

Lyn.

Le jeudi 1 mars 2018 05:23:37 UTC+11, David Tan a écrit :