0

I've been working with this query to achieve the results I wanted, which I have. During this time I started using PDO and at first worked by just having one varibale to prepare inside the query. While testing, I ended up using two more variables inside the query but left them out of the prepared statement until I was finished.

Now that I have the query completed, I went on and tried preparing the three variables as I did with the first one but an error was displayed when I ran the query:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '':cdrLastTable' ON a.id = b.`source_customer_billing_zone_' at line 2

I noticed the error was coming from one of the three prepared variables I needed for my query, but the thing is that the one that's failing is the only one placed before the WHERE clause. Excluding it from the prepared statement works fine. After reading and looking at exaples I could not find any with a variable before the WHERE clause so my question is:

Can you prepare variables before the WHERE clause? If this is not possible, then what would be the point on using PDO for this query?

I'm assuming that this issue does not relate to the query or any syntax error since removing the variable from the prepared statement just works, but I could be wrong as well.

This is the query with all variables prepared (the one that gives off the error):

$stmtOutgoing = $connAccounting->prepare("SELECT a.`zone` AS zone, init_time, a.`detail` AS sku, b.`source_external_subscriber_id`, count(*) AS llamadas, SUM((CEIL(b.`duration`))) AS duracion, SUM((b.`source_customer_cost`)/100) AS total, SUM((b.`source_customer_cost`)/100)/(SUM((CEIL(b.`duration`)))) AS precio 
                                        FROM `billing`.billing_zones_history a INNER JOIN :cdrLastTable 
                                        ON a.`id` = b.`source_customer_billing_zone_id` 
                                        WHERE source_external_subscriber_id = :source_external_subscriber_id AND init_time >= :queryLastTstmp
                                        GROUP BY a.`detail` 
                                        ORDER BY total desc");
                        $stmtOutgoing->execute(array('source_external_subscriber_id' => $source_external_subscriber_id, 'cdrLastTable' => $cdrLastTable, 'queryLastTstmp' => $queryLastTstmp));

You can see the error message points directly at :cdrLastTable right after the INNER JOIN, and effectively removing it like this:

$stmtOutgoing = $connAccounting->prepare("SELECT a.`zone` AS zone, init_time, a.`detail` AS sku, b.`source_external_subscriber_id`, count(*) AS llamadas, SUM((CEIL(b.`duration`))) AS duracion, SUM((b.`source_customer_cost`)/100) AS total, SUM((b.`source_customer_cost`)/100)/(SUM((CEIL(b.`duration`)))) AS precio 
                                        FROM `billing`.billing_zones_history a INNER JOIN $cdrLastTable 
                                        ON a.`id` = b.`source_customer_billing_zone_id` 
                                        WHERE source_external_subscriber_id = :source_external_subscriber_id AND init_time >= :queryLastTstmp
                                        GROUP BY a.`detail` 
                                        ORDER BY total desc");
                        $stmtOutgoing->execute(array('source_external_subscriber_id' => $source_external_subscriber_id, 'queryLastTstmp' => $queryLastTstmp));

Makes the statement work.

For some context:

A new table is created each month, hence the need to store the name in a variable unlike the first table which has a 'static' name.

The server is running 10.3.23-MariaDB and PHP 7.2.33.

I have tried quoting the variable like this: ':cdrLastTable' but did not work.

Thanks for the help!

Berny
  • 121
  • 14
  • 2
    Identifiers (column/table names) cannot be bound as parameters (only for values). – mario Nov 11 '20 at 09:21
  • 1
    there is no point in having a variable table name in the first place – Your Common Sense Nov 11 '20 at 09:22
  • I need to store it inside a variable because a new table is generated each month, the name uses the current Ym, for example, the name of the tabe for this month would be cdr_202010 but this is one of two querys. This one is for values from the past month (cdr_202009) so I need to generate the table's name using code depending on the current date. Is there other way to do it without using a variable? would the second query pose a possible entry point for sql injection? – Berny Nov 11 '20 at 09:27
  • Note about the other query: they are both identical other than the two variable names (cdrLastTable would be cdrCurrentTable for exaple), that's why I only posted one but I'm having the same issue on both – Berny Nov 11 '20 at 09:27
  • 1
    @Berny If your `$cdrLastTable` variable is safe (not coming from user input) then you can use it directly like in your second query. – Michal Hynčica Nov 11 '20 at 09:30
  • Yeah it's safe, that variable is generated via code whenever the query is run. Thank you all for clearing this up! – Berny Nov 11 '20 at 09:32
  • 1
    because it makes you to add a useless variable in your query and put you in a fix? where it must be just another condition in the WHERE – Your Common Sense Nov 11 '20 at 10:36
  • Excuse my lack of knowledge but I don't see how to do what you propose. I need the table for the INNER JOIN for I need data belonging to two different tables. Each month the second table 'closes' and automatically a new one is created. These tables store call info, so with each billing cycle there is a new tabe. I do not have direct control over this matter, however I could present this other way of doing it. What you suggest is to store everything on one table period and access relevant data for the current cycle using `where` clause? – Berny Nov 11 '20 at 10:55
  • 1
    Why are you creating a whole new table every month instead of just storing which month it is in a column in a single table? Variable tables are almost never the best way to do it. – GrumpyCrouton Nov 12 '20 at 12:55
  • I see @GrumpyCrouton, when I arrived at this project all this infrastructure had already been in place for a couple of years, that's why I have no direct control control over it. I just rolled with what was established. I will aks why is the db system designed like so and perhaps I can share what I learned here. At first it made sense for my due to my lack of laboral experience and the fact of the company being a telephone one with billing cycles. I even tought it was a smart move! Nontheless, huge thanks for clarifying this up! – Berny Nov 12 '20 at 13:30
  • @Berny Yeah, having multiple tables like this is bad for a few reasons. 1) Tables themselves have more overhead than just adding more rows, so your storage space is being wasted (It's not a huge deal because of how cheap storage can be...) and 2) It makes doing queries like this much much harder and less safe for no real gain. – GrumpyCrouton Nov 12 '20 at 13:48

0 Answers0