String literals in mysql queries stored as TEXT

[nickknowledge]

SQL queries which were working fine now have stopped working if I store them in a mysql TEXT field.

So:

$sql_student = "SELECT s.student_user_id, u.user_firstname, u.user_lastname from students as s JOIN users as u ON u.ID = s.student_user_id WHERE s.student_id = '$student_id'";

Becomes:

$sql_report_sql = "select report_sql from report_styles where report_style_id = '$report_style'";

$sql_student = $ez->get_var($sql_report_sql);

If I echo the statement out, it's:

SELECT s.student_user_id, u.user_firstname, u.user_lastname from students as s JOIN users as u ON u.ID = s.student_user_id WHERE s.student_id = '$student_id'

But '$student_id' is not interpreted, and the query fails.

Do I need to do some kind of string replace, or escape with backslashes?

Thanks for any help.

[ckarg]

You probably have to play around with eval:

$sql_report_sql = q{select report_sql from report_styles where report_style_id = '$report_style'}; # or from TEXT field
$report_style = 'fancy';
$final_query = eval qq{"$sql_report_sql"};
print $final_query,"\n"; # or send it to sql....

But there's more to this. You need to be careful with where your data comes from. If $report_style can't be trusted (e.g. it comes from a web form), then the above creates an sql-injection vulnerability. If you're using DBI, then parameter binding is the way to go.

good luck!