Parameter in SELECT or JOIN

[andrewteg]

I know this isn't ideal, but I have a query where it basically is joining up a lookup table and the $child_pk var could be any number of a few strings like email, url, etc. I'd like to join on that field and have it be a variable so I'm not rewriting the same query so I have it as:
LEFT JOIN $child_mod ON child_id = $child_pk

If I change it to LEFT JOIN $child_mod ON child_id = ? and add a parameter to the query $db->select($strSQL, [$child_pk]) style then I get an error of "ERROR: invalid input syntax for type integer" as I suspect it is doing ON child_id = 'email' instead of ON child_id = email as it parses out that $child_pk is a string.

Is there any way around this, or is it best to leave LEFT JOIN $child_mod ON child_id = $child_pk and run it through a custom function for security's sake before I call the select? I figure I probably should run it through a check of some sort anyways as it's likely to show up in an AJAX call, but wasn't sure if there was a better way to handle this in PHP-DB.

Thanks.

[ocram]

Thank you!

This is a good example of what one might want to do with more dynamic queries, and of the challenges that come with that.

Right now, the only solution is indeed your own sanitization or escaping, although you are right that you should probably use an explicit allow list to check which tables and columns you want to support.

In the long term, the only elegant solution here is a full query builder that lets you assemble your queries from dynamically from scratch, for all clauses of a statement.

Let’s keep this open until we have a query builder here.