Passing PHP variables into MySQL

Question

I have a function in PHP which inserts values into MYSQL tables.

function insertRow($db, $new_table, $ID, $Partner, $Merchant)
{
    $insert = "INSERT INTO " .$new_table. " VALUES(number, "string", "string")"
    $q = mysqli_query($db, $insert);
}

I am struggling customizing the VALUES part. I need the number, string, and string to be ID, Partner, and Merchant variables from PHP respectively.

I've tried

function insertRow($db, $new_table, $ID, $Partner, $Merchant)
{
    $insert = "INSERT INTO " .$new_table. " VALUES(" .$ID . $Partner . $Merchant . ")";
    $q = mysqli_query($db, $insert);
}

as well. But it doesn't seem to work because for SQL the string values must be surrounded with quotes. But if I change the code so that its ."$ID" . "$Partner" . "$Merchant" . ")"; and thus the variables are in quotes as needed, they are no longer PHP variables. How do I get my PHP variables to be included in quotes so that I can execute the SQL correctly?

Read up on database escaping.

mario
– mario

2012-06-22 23:51:22 +00:00
Commented Jun 22, 2012 at 23:51 — mario
– mario, Commented Jun 22, 2012 at 23:51

lonesomeday · Accepted Answer · 2012-06-22 19:54:47Z

5

The other answers, using concatenation, are the simple ones. The best one is to use prepared statements, which will make your code significantly more secure.

$insert = "INSERT INTO " .$new_table. " VALUES(?, ?, ?)";
$q = mysqli_prepare($db, $insert);
mysqli_stmt_bind_param($q, "iss", $ID, $Partner, $Merchant);
mysqli_stmt_execute($q);

Doing parameterised queries means your query and the data are sent separately. This means that the structure of the query already exists, and so cannot be altered by anything else inserted in the data, which means you are safe from SQL injection.

See the PHP manual:

answered Jun 22, 2012 at 19:54

lonesomeday

239k54 gold badges330 silver badges329 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rachael Over a year ago

This is the correct way to do this. Without the parameters you are opening yourself up to attacks.

alpham8 · Accepted Answer · 2017-05-10 13:28:35Z

Well, there are many methods to possibilities to do this:

String concatenation

be aware of the data type that the database except. In your question, you missed out the comma to seperate the values:

$insert = 'INSERT INTO ' . $new_table . ' VALUES(' . $ID . ', \'' . $Partner . '\', \'' . $Merchant '\')';

There are many ways to modify the string concatenation example. Consider the php documentation about single and double quotes. In you example you used double quotes in the meaning of single quotes. With double quotes you can put variables directly into it. So I changed it to single quotes.

sprintf

$insert = sprintf('INSERT INTO %s VALUES(%s, \'%s\', \'%s\');', $new_table, $ID, $Partner, $Merchant);

see the documentation for more info about this handy function: http://www.php.net/sprintf

Prepared Statements

I would rather advice you to use this method because of SQL Injection. In the example above you need to take care about string escaping. May work with mysql_real_escape. But it may not work in all scenarios.

So, prepared statements are a lot better:

$insert = 'INSERT INTO ' .$new_table. ' VALUES(?, ?, ?)';
$q = mysqli_prepare($db, $insert);
mysqli_stmt_bind_param($q, "iss", $ID, $Partner, $Merchant);
mysqli_stmt_execute($q);

PS: Using the object oriented way would be also better for prepared statement, because you got more opportunities to do this. See the documentation for MySQLi class style and for PDO.

Mike · Accepted Answer · 2012-06-22 19:49:02Z

0

Concatenation

$insert = "INSERT INTO " .$new_table. " VALUES(".$ID.", '".$Partner."', "."'"$Merchant"')";

answered Jun 22, 2012 at 19:49

Mike

7812 gold badges7 silver badges14 bronze badges

Comments

jcho360 · Accepted Answer · 2012-06-22 19:49:02Z

0

Try this:

function insertRow($db, $new_table, $ID, $Partner, $Merchant)
{
    $insert = "INSERT INTO " .$new_table. " VALUES( '" .$ID."','" . $Partner."','". $Merchant . "')";
    $q = mysqli_query($db, $insert);
}

Or you can use

function insertRow($db, $new_table, $ID, $Partner, $Merchant)
{
    $insert = "INSERT INTO " .$new_table. " VALUES( '$ID','$Partner','$Merchant')";
    $q = mysqli_query($db, $insert);
}

answered Jun 22, 2012 at 19:49

jcho360

3,7491 gold badge18 silver badges25 bronze badges

Comments

Niet the Dark Absol · Accepted Answer · 2012-06-22 19:49:19Z

0

Um... it being a query makes no difference to the fact that it's just a string like any other. You were perfectly capable of adding in the $new_table value, so what's stopping you from doing the others?

$insert = "INSERT INTO ".$new_table." VALUES (".$ID.", '".$Partner."', '".$Merchant.")";

This assumes you already took the necessary steps to sanitise the values.

answered Jun 22, 2012 at 19:49

Niet the Dark Absol

326k86 gold badges480 silver badges604 bronze badges

Comments

icanc · Accepted Answer · 2012-06-22 19:51:23Z

0

Try this:

$insert = "INSERT INTO $new_table VALUES(number, '{$string}', '{$string}')";

answered Jun 22, 2012 at 19:51

icanc

3,5873 gold badges37 silver badges45 bronze badges

Comments

Fahim Parkar · Accepted Answer · 2012-06-22 19:52:55Z

0

$insert = "INSERT INTO $new_table VALUES($ID,'$Partner','$Merchant')";

answered Jun 22, 2012 at 19:52

Fahim Parkar

31.8k46 gold badges168 silver badges285 bronze badges

Collectives™ on Stack Overflow

Passing PHP variables into MySQL

7 Answers 7

1 Comment

String concatenation

sprintf

Prepared Statements

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

1 Comment

String concatenation

sprintf

Prepared Statements

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related