SQL syntax: passing variable to SQL select query

Question

I'm trying to pass variables into the select query. Quer is below

$Email = $_POST["Email"];
$Username = $_POST["User_Name"];
$FirstName = $_POST["First_Name"];
$Password = $_POST["Password"];


$CreateTable = "CREATE TABLE IF NOT EXISTS "+$Username+" (
address_id int(11) NOT NULL 
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;" ;

But the table wasn't creating. Where I missed?

Thanks your valuable time.

1. `+` is the wrong operator, php uses `.` for string concatenation. And 2. you should _never_ construct a query like that, you open your code mile wide for sql injection attacks. Learn about the benefit of using prepared statements with parameter binding. — arkascha, Jun 25 '17 at 09:11
Creating a separate table for each use seems like a really bad practice. — Gordon Linoff, Jun 25 '17 at 11:30
Yeah, If we use this code it is easy to attack everyone. The reason why I used this code is easy to ask the question. Any reference to this question is highly appreciated. Thank you guys — E J Chathuranga, Jun 25 '17 at 12:13

score 2 · Answer 1 · answered Jun 25 '17 at 09:12

As you would not be able to use prepared statements with this type of query you should perhaps attempt to remove potentially harmful characters from the supplied user input.

$email = filter_input( INPUT_POST, 'Email', FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH );
$username = filter_input( INPUT_POST, 'User_Name', FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH );
$firstname = filter_input( INPUT_POST, 'First_Name', FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH );
$password = filter_input( INPUT_POST, 'Password', FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH );


/* Strip any non alphanumeric charachters and replace space with underscore */
$username = preg_replace('@^[\da-z]$@i','', str_replace( ' ', '_', $username ) );


$sql = "CREATE TABLE IF NOT EXISTS `{$username}` (
    address_id int(11) NOT NULL 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;";

$db=new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );
$db->query( $sql );

score 1 · Answer 2 · answered Jun 25 '17 at 16:40

You are using '+' symbol to connect two string (which will not work in php).

You should use '.' to connect two strings.

See answer : How to combine two strings together in PHP?

Your SQL statement should look like this :

$CreateTable = "CREATE TABLE IF NOT EXISTS ".$Username." (
address_id int(11) NOT NULL 
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;" ;

BTW, It's not recommended to execute sensitive queries such as creating (NOR DELETING) tables within your php script.

score 1 · Accepted Answer · answered Sep 21 '17 at 04:41

1

You should check your PHP file. And try

$tableUser = "CREATE TABLE IF NOT EXISTS ".$Username."(
index int(11) NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;" ;

answered Sep 21 '17 at 04:41

kiwDroid

46
2

SQL syntax: passing variable to SQL select query

3 Answers3