I have a very dynamic scenario that requires met to store queries as a string in the database, the problem I have is that the following have resulted in a syntax error where the variables are creating a MySQL error with the error showing the variable name and not the value. The SQL is pulled from the database then set up like so:

$database1_sql= $DB_con2->prepare("$sql1");
$database1_sql->execute();


I have tried the following all resulting in the same issue, and I am stumped:
`
select columns

FROM '.$database_1_old.'.'.$table1.' as dsp

or

FROM '".$database_1_old."'.'".$table1."' as dsp

or

FROM $database_1_old.$table1

or

FROM '" . $database_1_old . "'.'" . $table1 . "'
`
I have also tried using the eval function thinking that might get around the errors and use the variables as intended, but I get an t string error in the first few characters in my column list.

I have used variables in queries before, but not in this manner where the query is a row from a database.

What am I missing?

Thanks!

    If the variable names are showing up then it means variable interpolation is not happening, which would imply that you're using single quotes somewhere that double quotes would be needed.

    Worrying are the quotes in this line

    $database1_sql= $DB_con2->prepare("$sql1");

    If you think you need quotes to make a string out of something that should already be a string then something is wrong somewhere.

    To populate a pre-existing string with values at run-time, treat it as a template and use either sprintf(), vsprintf(), or str_replace() to get the values into it.

    I hope that these dynamic database and table names are NOT coming from external data without being validated that they are only and exactly permitted choices in order to prevent sql injection or to allow a hacker to grab the contents of any of your database/tables?

    In addition to Weedpacket's concern about putting unnecessary quotes around a bare php variable, why are you preparing and executing a non-prepared query?

      Thanks to both of you, I have tried this:

      $database1_sql= $DB_con2->prepare("$sql1");

      without the quotes too as part of trying to get this to work. I added them as the row does not have double quotes.
      The values in the query are not from an external data source, and are validated prior to this query.

      When I print the SQL there are no single quotes apart from those examples above I mentioned.

      I will look at vsprintf and see if that works, but I am curious as to how to treat the variables as such and break them out of their literal markup.

      Thanks

      pbismad To populate a pre-existing string with values at run-time, treat it as a template and use either sprintf(), vsprintf(), or str_replace() to get the values into it.

      sprintf et al. to would look like a more robust templating method to what appears to be the current choice (which seems to involve storing not just database queries in the database but database queries with embedded PHP code in the database to be later executed by PHP).

      pbismad why are you preparing and executing a non-prepared query?

      As far as that goes, it looks like the table and database names aren't known in advance and have to be provided at run time. Those can't be parameterised in a prepared query because the DBMS needs them to prepare the query.

            DesignerSeries Thanks to both of you, I have tried this:

            $database1_sql= $DB_con2->prepare("$sql1");

            without the quotes too as part of trying to get this to work.

            That's just it: the quotes are useless. Having them there is a symptom that suggests a lack of a clear idea of what's actually going on. And without having one of those you're just throwing random stuff (like putting in quotes or taking them out) and hoping some of it sticks.

            And, let's be clear here, you have a better idea of what your code is and what it does and what it should be doing than we do.

                Write a Reply...