How can I dynamically generate a table name in a SQL query?

Question

I have a SQL Query that uses MySQL database to get information from. The table names are first constant with a different IMEI number as it's end.

gs_object_data_12345678

The 12345678 is identified as $_GET in my script but when I try change table name it does not show the data in that table

$imei = $_GET['imei'];

 $result = $db->query("SELECT * FROM gs_object_data_'$imei' LIMIT 200 ");

If I use the string as below it works

$result = $db->query("SELECT * FROM gs_object_data_12345678 LIMIT 200 ");

I need to change the table name with the $_GET information

The URL alarm_action.php?id=58543&reg=****&imei=862202228007105 has IMEI in it so the information should pull thru

Abdulla Nilam · Answer 1 · 2022-12-28T08:28:14.067

-2

use like this

$imei = $_GET['imei'];
$result = $db->query("SELECT * FROM gs_object_data_" . $imei . " LIMIT 200 ");

or

$result = $db->query("SELECT * FROM gs_object_data_{$imei} LIMIT 200 ");

Be Aware How does the SQL injection from the "Bobby Tables" XKCD comic work?

In protected way

$imei = $_GET['imei'];

$stmt = $db->prepare("SELECT * FROM gs_object_data_? LIMIT 200");
$stmt->bind_param("s", $imei);
$stmt->execute();

$result = $stmt->get_result();

edited Dec 28 '22 at 08:28

answered Dec 28 '22 at 08:24

Abdulla Nilam

36,589
17
64
85

Please don't suggest to be open to SQL Injection – Justinas Dec 28 '22 at 08:25
https://stackoverflow.com/questions/11312737/can-i-parameterize-the-table-name-in-a-prepared-statement – Nigel Ren Dec 28 '22 at 08:32
@NigelRen it's outdated and now we can. – Abdulla Nilam Dec 28 '22 at 08:35
2

That's interesting, which version of PHP and MySQL? It would be useful to add this to the question I linked to if things have changed. – Nigel Ren Dec 28 '22 at 08:44
@NigelRen https://stackoverflow.com/questions/2838490/a-table-name-as-a-variable – Abdulla Nilam Dec 28 '22 at 09:02
OK - that refers to SQL Server and not MySQL. – Nigel Ren Dec 28 '22 at 09:03
@NigelRen i thought you would say that ha ha https://stackoverflow.com/questions/8809943/how-to-select-from-mysql-where-table-name-is-variable – Abdulla Nilam Dec 28 '22 at 09:05
OK - that's a stored procedure and not called from PHP as a prepared statement. – Nigel Ren Dec 28 '22 at 09:06
And even here, if you use `SELECT * FROM gs_object_data_{$imei} LIMIT 200` and pass this as a variable, it also works. Because the final output of the variable prints the proper table name. Means complete SQL – Abdulla Nilam Dec 28 '22 at 09:09
Which goes back to your first attempt which is prone to SQL Injection. – Nigel Ren Dec 28 '22 at 09:11
Why not `$pdo->prepare('SELECT * FROM users WHERE imei = :imei'); $stmt->bindParam(':imei', $_GET['imei']);` – Abdulla Nilam Dec 28 '22 at 09:32

How can I dynamically generate a table name in a SQL query?

1 Answers1