[RESOLVED] Insert a variable (value) into a $sql statement retrieved from a MySQL table?

Joseph_Sliker

Hi;

I'm trying to make my site more efficient by calling queries from a table rather than hard-coding them into each page.

IE. say I have a mysql table to store queries with the fields ID, SQLname, SQL.

SAMPLE DATA RECORD
ID: 1
SQLname: Test Query
SQL: SELECT * FROM TABLE2 WHERE USER='$USER'

(Note, the SQL field is defined as a TEXT field).

So in the script I post the value of $USER (ie. $USER='joe'😉

When I try to call and run the script in the same page, the query does not work, because it is trying to run, literally:
"SELECT FROM TABLE2 WHERE USER='$USER' " instead of "SELECT FROM TABLE2 WHERE USER='joe ".

I suppose it something where I have to use some special characters or something around the string or the variable name that I save in the SQL field?

Anybody got a clue for me on this one? Thanks.

Joseph_Sliker

Never mind. The php "eval()" function came to mind:

eval("\$SQL = \"$SQL\";");

This converts the literal SQL field string into php code....and therefore adds in any of the $values that were defined prior to it in the script.

johanafm

Which allows for very nasty behaviour. NEVER eval things unless you really have to. And when you really have to, as a rule of thumb, still don't.

What if I register as a user on your site calling myself

\".shell_exec('ls').\"

Which eventually becomes

$user = "\".shell_exec('ls').\"";
$sql = "SELECT * FROM User WHERE user = $user";
eval("\$sql = \"$sql\";");

Where the eval'd code is

$sql = "SELECT * FROM User WHERE user = ".shell_exec('ls')."";

And now image I ran something more nasty than ls. I could for example download a php script with scp and then execute it. Or rm -f. Or add a new user to the database. Well, some things possibly being restricted by settings: safe mode being enabled, or db user not having insert or update privileges to the db user table etc. But you still run an unnecessary risk.

Joseph_Sliker

Thank you for the warning. In that I am the controller of the table that contains my SQL statements I think I am safe. Users would not have any access to forms required to insert any new records in that table, but I do appreciate the warning.