How to put a php variable in a mysql query?

Question

For example:

$query = "INSERT INTO tabela (data, assunto, destino, elaborado) VALUES('{$data}', '{$assunto}', '{$destino}', '{$elaborado}')";

Where is tabela, i want to put a variable in place, because depending on what the user chooses, will use a different table.

I’ve tried it with quotes, with dots, with keys...

Answer 1

In PHP when you have a string wrapped with double quotes. The PHP interpreter will scroll through it and check if it has no variables to replace.

<?php
$tabela = 'Tabela1';
$data = '2018-06-20';
$assunto = 'Assunto';
$destino = 'Destino';
$elaborado = 'Elaborado';

$query = "INSERT INTO $tabela (data, assunto, destino, elaborado) 
VALUES('$data', '$assunto', '$destino', '$elaborado')";

echo $query;

The excerpt echo $query; will print INSERT INTO Tabela1 (data, assunto, destino, elaborado) VALUES('2018-06-20', 'Assunto', 'Destino', 'Elaborado')

Answer 2

Thiagosilr’s answer is correct, just put the variable names inside the string with double quotes. However, I suggest not using this method to form darlings to be executed at your bank, as it makes you vulnerable to SQL injection attacks.

For example, if the user has control of the variable content $assunto through a form, could fill it as '1'); DROP DATABASE ... and try to destroy your bank only of pirraça.

A way to execute that same INSERT ensuring that input variables do not cause damage is using prepared statements, as demonstrated in the responses to this question from the SOE.

So, if you are using Mysqli, you instantiate an object of statement using the method prepare() of your connection object, specifying the gaps for parameters of bind using question marks, then pass the parameters in order in the method bind_param() of statement finally executes it:

//cria o statement
$statement = $dbConnection->prepare("INSERT INTO $tabela (data, assunto, destino, elaborado) VALUES(?, ?, ?, ?)");

//passa as  variáveis para preencher os pontos de interrogação
//o parametro 's' significa que você está passando uma string. É como um printf...
$statement->bind_param('s', $data); 
$statement->bind_param('s', $assunto); 
$statement->bind_param('s', $destino); 
$statement->bind_param('s', $elaborado);

//executa o statement
$stmt->execute();

Note that a Prepared statement only supports the parameterization of values of a query. Thus, the table name variable must be passed with simple string concatenation as in Thiago’s answer. I imagine it is somewhat unlikely that such a variable is an attack vector, since it is unusual to allow the user of an application to decide on which table of the system you want to query.

Answer 3

One way is to use it as follows:

$query = "INSERT INTO tabela (data, assunto, destino, elaborado) VALUES({$data}, {$assunto}, {$destino}, {$elaborado})";

Without the simple quotes.