0

I have a problem here, my code is:

$query = $con2->createQueryBuilder()->select('*')
                ->from('`blog_entry`', 'p')
                ->where('WHERE url->"$.?" = ?')
                ->setParameter(0, $request->getLocale())
                ->setParameter(1, $entryUrl);

The url field is a JSON field, and it contains like this: {"es": "url.html"}

But it appears the next error:

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

I think that the part of url->"$.?" is the error, but I don't know how to pass the parameter there, any ideas?

Marc Garcia
  • 1,388
  • 1
  • 9
  • 21

2 Answers2

2

You can't bind to a JSON path like that. From the manual:

Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth.

You will need to change your where() call to include the path instead, and remove that parameter value:

$query = $con2->createQueryBuilder()->select('*')
                ->from('`blog_entry`', 'p')
                ->where('WHERE url->"$.' . $request->getLocale() . '" = ?')
                ->setParameter(0, $entryUrl);

Update

Inspired by FMK, I've done a bit of testing on my own server, and discovered that this will work:

$query = $con2->createQueryBuilder()->select('*')
                ->from('`blog_entry`', 'p')
                ->where('WHERE JSON_EXTRACT(url, ?) = ?')
                ->setParameter(0, '"$.' . $request->getLocale() . '"')
                ->setParameter(1, $entryUrl);
Nick
  • 138,499
  • 22
  • 57
  • 95
  • It works! But this can be dangerous? To put directly in SQL without parameters? – Marc Garcia May 07 '19 at 09:23
  • 1
    @MarcGarcia yes, it is more dangerous, so you would need to check that the value of `$request->getLocale()` was valid. – Nick May 07 '19 at 09:24
  • 1
    @MarcGarcia I think I've figured out a way around it. See my edit. – Nick May 07 '19 at 10:02
  • Nope: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; Don't worry, I will use the first part! – Marc Garcia May 07 '19 at 10:45
  • @MarcGarcia it's working on my server (although not using laravel, just the direct mysqli interface), can you post the exact error message you got? I might be able to resolve it. As you say in your first comment, this would be less dangerous. No rush, I'm just curious... – Nick May 07 '19 at 13:05
  • 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 'WHERE JSON_EXTRACT(url, '\"$.es\"') MySQL v: Version: 5.7.24 – Marc Garcia May 08 '19 at 08:44
  • Looks like you need to change `'"$.' . $request->getLocale() . '"'` to `'$.' . $request->getLocale()` – Nick May 08 '19 at 12:21
0

Have you tried something like this:

WHERE JSON_EXTRACT('url','$.?') = '?'
FMK
  • 1,062
  • 1
  • 14
  • 25