CODEWITHSUNDEEP
phpmysql
Atul Agrawal
Atul Agrawal

Reputation: 125

How to use a variable as table name at the time of create table query in MySQL?

I have a variable that takes the value from previous page, and I want to use that value as my table name. The value passes is an integer so therefore I want my table name as schedule_12 if the value passed is 12. I have used the code below, it does not give any kinds of error but the table was not created in my database. Is there any way to solve this ? Thanks in adance

 <?php
$trNum=$_REQUEST["tr_num"];
 include ("dbConnect.php");


@mysql_query("create table 'schedule_".$trNum."'(sid int primary key 
auto_increment,st_name varchar(20), arr_time varchar(5), dep_time 
varchar(5), halt varchar(5), dist int, day int);");

echo "Schedule created successfully";


?>

Upvotes: 0

Views: 103

Answers (2)

ArtisticPhoenix
ArtisticPhoenix

Reputation: 21681

Something like this

<?php
$trNum=$_REQUEST["tr_num"];
include ("dbConnect.php");


mysql_query("
    CREATE TABLE schedule_".(int)$trNum."
        (
            sid int primary key auto_increment,
            st_name varchar(20), arr_time varchar(5),
            dep_time varchar(5),
            halt varchar(5),
            dist int,
            day int
        )"
);

echo "Schedule created successfully";

Note as I said in the comments, table names should not be quoted like strings. Don't use the @ sign because it will suppress errors, which we need to make sure things work. mysql_* family of functions are deprecated consider using mysqli_* or PDO.

Lastly and very important, because you are concatenating a variable into SQL it leaves you open to SQL injection attacks. In this case there probably isn't much you can do ( like prepared query etc.. ). That variable is not safe because its supplied by the Client, though $_REQUEST. So you need to check it before using it.

I showed you casting but you can check it several ways including Regx

    if( preg_match('/^[0-9]+$/', $trNum ) ){
          ...create table code
    }

This regx checks that it begins and ends with Numbers and only contains Numbers, which would limit what could be injected into the SQL. There may be better ways to sanitize this, but this should be sufficient in this case.

Casting it is ok, but it may cast a string to 0 and try to create a table named schedule_0 if someone tried to hack it, which is not Ideal. However it's better they get the error message for creating a table that exists then the alternative.

For a quick example of how this could be exploited, a user could supply a value of 0( id int ); UPDATE users SET password = 'password'; -- then your query becomes this.

mysql_query("
    CREATE TABLE schedule_0( id ind ); UPDATE users SET password = 'password'; --
        (
            ... orignal fields ..
        )"
);

So the -- is the start of a comment in SQL, everything past it will not be ran by the DB. Then the 0( id int ); completes your original query creating a minimal table. Lastly we sneak in an Update to change the passwords of all your application users. And then we can login as anyone we want and do all sorts of nasty things.

This is a simplified and imagined case, but it's not to far from what is possible when leaving yourself wide open...

Upvotes: 1

nico
nico

Reputation: 2121

The comments pretty much gave you the answer (as well as some good suggestions). Here is how you'd correct it (using PDO). Notice I removed the quotes in the query, don't forget to sanitise $trNum if it is passed by a user, otherwise SQL injections are possible.

<?php
$trNum=$_REQUEST["tr_num"];

$db = new PDO("dbtype:host=yourhost;dbname=yourdbname;charset=utf8","username","password");
$db->query("create table schedule_" . $trNum . " (sid int primary key 
auto_increment, st_name varchar(20), arr_time varchar(5), dep_time 
varchar(5), halt varchar(5), dist int, day int)");

echo "Schedule created successfully";

?>

Upvotes: 1

Related Questions