Unable to query JSON inside SQL statement when using external users.

[mikewcaldwell]

I'm currently setting up Q2A to work with an existing user database where my user sessions database (built on node.js:express-session) uses JSON to store information about the session in a database.

When trying to read this data, I would like to be able to query JSON inside my SQL query (simple example follows, code is in qa-external-users.php::qa_get_logged_in_user()). The below query works when called directly through mysql.

'SELECT data->'$.cookie' FROM sessions WHERE session_id=#;'

However, in qa-db.php::qa_db_apply_sub, ^, $ and # are reserved with no way to escape them (as far as I can tell).

Would it be better to use mysqli bind_param and the built-in support for prepared statements? https://www.php.net/manual/en/mysqli-stmt.bind-param.php

Thanks.

[pupi1985]

That's part of 1.9, but it has not been released. You can try to extract the new query logic from there, if you have some free time :)

Other approaches:

• Get the full JSON and decode it in PHP (should be the option that makes the most sense, unless the JSON is huge)
• I haven't tested this but probably you could write something like data->$ and send $.cookie as a parameter
• Turn the JSON column into a more normalized approach and create the corresponding tables (it is an option, like it or not :smile:)

[mikewcaldwell]

That's good to hear, thanks for the info and quick response! I will go ahead and close this issue.