This is an archived copy of the forum for reference purposes

Dynamic SQL

posted by anonymous 8 years ago

Hi,

I am trying to build a dynamic SQL query.

This works

$result = $db->query("SELECT * FROM inventory WHERE %r = %i LIMIT 25", 'slots', 38);

How these dont

$result = $db->query("SELECT * FROM inventory WHERE %r %r %i LIMIT 25", 'slots', 'LIKE', 38);
$result = $db->query("SELECT * FROM inventory WHERE %r %r %s LIMIT 25", 'slots', 'LIKE', '38%');

Any help appreciated :(

Thanks

I suggest you to use fCore::enableDebugging() in order to debug and see what is the result of the interpreted query. You can allways enable debug only in fDatabase (see fDatabase#Debugging)

BTW, %r is used to escaping identifiers, not other strings like modifiers or values, and here is where your query goes wrong, I think (see fDatabase#EscapingIdentifiers)

Try something like this:

$result = $db->query("SELECT * FROM inventory WHERE %r LIKE %s LIMIT 25", 'slots', '38%');

posted by xoan 8 years ago

Indeed, it would likely be the case that it's failing because you are attempting to escape the LIKE as if it were an identifier (such as a table or column name).

On this note, it may perhaps be useful to add a %u placeholder for unescaped data (if the user really needs such a thing).

posted by mattsah 8 years ago

I was thinking about the idea of %u this morning. Do you think %u for unescaped, or %r for raw would be more intuitive?

posted by wbond 8 years ago

Any thoughts on this msahagian or xoan?

posted by wbond 8 years ago

I don't use fDatabase class directly (I'm more fActiveRecord :), but I think %u for unscaped sounds good.

posted by anonymous 8 years ago

Perhaps I'm missing something, but isn't this thread based on the fact that %r is already used for identifiers? Using %r would cause some serious breaks in backwards compatibility in terms of security it would seem. %u seems suitable to me.

posted by mattsah 8 years ago

Haha, you are completely correct. I'm not sure how I forgot about %r being for identifiers. I should be adding this %u code soon.

posted by wbond 8 years ago