How to use sprintf to mount an SQL query?

Asked

Viewed 410 times

0

What is the correct way to use sprintf in mysqli and make the return IF?

$rs = $mysqli->query(sprintf("INSERT INTO perguntas (chave, nome, email, idade, estado_civil, profissao, religiao, assunto, pergunta, `data`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                        mysqli_real_escape_string($chave),
                        mysqli_real_escape_string($_POST['nome']),
                        mysqli_real_escape_string($_POST['email']),
                        mysqli_real_escape_string($_POST['idade']),
                        mysqli_real_escape_string($_POST['estado_civil']),
                        mysqli_real_escape_string($_POST['profissao']),
                        mysqli_real_escape_string($_POST['religiao']),
                        mysqli_real_escape_string($_POST['assunto']),
                        mysqli_real_escape_string($_POST['pergunta']),
                        mysqli_real_escape_string($data)));

    if (mysqli_query($rs)) {
        echo "<script>window.location = '".$baseURL."/enviar-aconselhamento&chave=$chave'</script>";
    }else{
        die('Error: ' . mysqli_error($mysqli));
    }

That way I’m doing above is returning error:

Error: You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near ' , , , , , )' at line 1

php mysql mysqli

  • You’re doing it wrong.

  • 2

    That’s my question...rsrs, how to do it properly... can help?

  • @Ivanferrer Can help?

  • 1

    Have you consulted the manual of PHP.net?

  • Why are you sprintf?

  • How to prevent SQL code injection into my PHP code, the sprintf() is a great solution to generate the sql template and not to pass the values in legacy codes is still acceptable.

Show 1 more comment

2 answers

2

First of all, sprintf() has nothing to do with mysqli().

Let’s cut to the chase.

In this passage:

$rs = $mysqli->query(sprintf("INSERT INTO perguntas (chave, nome, email, idade, estado_civil, profissao, religiao, assunto, pergunta, `data`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                        mysqli_real_escape_string($chave),
                        mysqli_real_escape_string($_POST['nome']),
                        mysqli_real_escape_string($_POST['email']),
                        mysqli_real_escape_string($_POST['idade']),
                        mysqli_real_escape_string($_POST['estado_civil']),
                        mysqli_real_escape_string($_POST['profissao']),
                        mysqli_real_escape_string($_POST['religiao']),
                        mysqli_real_escape_string($_POST['assunto']),
                        mysqli_real_escape_string($_POST['pergunta']),
                        mysqli_real_escape_string($data)));

Replace with this:

$columns = array('chave', 'nome', 'email', 'idade', 'estado_civil', 'profissao', 'religiao', 'assunto', 'pergunta', 'data');
$parameters = array($chave, 'nome', 'email', 'idade', 'estado_civil', 'profissao', 'religiao', 'assunto', 'pergunta', 'data');
$values[] = $chave;
foreach ($parameters as $k => $v)
    $values[] = (isset($_POST[$v])? mysqli_real_escape_string($_POST[$v]) : '');
$values[] = $data;

$sql = 'INSERT INTO table ('.vsprintf("`%s`,`%s`,`%s`,`%s`,`%s`,`%s`,`%s`,`%s`,`%s`,`%s`", $columns).')
 VALUES ('.vsprintf("'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s'", $values).')';

$rs = $mysqli->query($sql);

I used vsprintf() instead of sprintf() because vsprintf() supports array in the second parameter. This makes it easy to add parameters dynamically.

1


I changed the name of your database date field, because the word "date" is a reserved Mysql variable, and should not be used as a field name. No need to use the function sprintf(), The mysqli already has the necessary treatment, for values through the preparedStatment:

$data = array(
              'chave' => mysqli_real_escape_string($chave),
              'nome'  => mysqli_real_escape_string($_POST['nome']),
              'email' => mysqli_real_escape_string($_POST['email']),
              'idade' => mysqli_real_escape_string($_POST['idade']),
              'estado_civil' => mysqli_real_escape_string($_POST['estado_civil']),
              'profissao' => mysqli_real_escape_string($_POST['profissao']),
              'religiao' => mysqli_real_escape_string($_POST['religiao']),
              'assunto' => mysqli_real_escape_string($_POST['assunto']),
              'pergunta => 'mysqli_real_escape_string($_POST['pergunta']),
              'data_pub' => mysqli_real_escape_string($data_pub)
            );

$query = "INSERT INTO perguntas (`chave`,
                                 `nome`,
                                 `email`,
                                 `idade`,
                                 `estado_civil`,
                                 `profissao`,
                                 `religiao`,
                                 `assunto`,
                                 `pergunta`,
                                 `data_pub`)
          VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";

$stmt = $mysqli->prepare($query);
$stmt ->bind_param("ssssssssss", $data['chave'],$data['nome'],$data['email'],$data['idade'],$data['estado_civil'],$data['profissao'],$data['religiao'],$data['assunto'],$data['pergunta'],$data['data_pub']);
$action = $stmt->execute();
$stmt->close();
if ($action) {
   echo "<script>window.location.href='{$baseURL}/enviar-aconselhamento&chave={$chave}'</script>";
} else {
  die('Erro: ' . mysqli_error($mysqli));
}

  • You did not need to change the column name. You can use the name "date". To avoid conflict with reserved names, escape with backsticks data. In fact, the original code of the question is already escaped.

  • 1

    This solution of yours is not the best way to get around the problem, I prefer to solve the problem at the root rather than having to treat all the data that will be used avoiding new conflicts. The best is always to optimize the architecture.

  • This is not "my solution". It is a basic feature of Mysql. It has nothing to do with optimizing structure and nothing to do with what you said.

  • I usually use an indexing pattern: created_at

  • 1

    I also do it for the same reason, to avoid conflict of reserved names and also because I don’t like to use backsticks.. But it’s not the case here and it’s not the focus of the subject, you know? Unnecessary to modify the structure of others. It was just to show the use of sprintf() and bam! rsrs

  • 1

    You don’t need sprintf() to the problem presented, so there is no need to show the use of it, and even if the subject is out of focus, I try to guide people in the best possible way, it is not about solving the problem only, I see no reason to hide information that has only to add solutions.

  • 1

    The focus of the question is on sprintf().. now the concept of what is right or wrong has nothing to do with.. concept is concept.. No one is required to use OOP, PDO, an X or Y function to solve a problem that can be solved in N ways... But anyway... it’s not wrong what you posted. I just asked why to change the name of the column. no need.. That’s all. That’s all..

  • The question itself is about using the sprintf(), but if you notice in detail of the question, the doubt of colleague James leads to another direction in his doubt, which suggests a more relevant answer to the problem presented.

  • And not once have I mentioned that you are required to use OOP or PDO, or an X or Y function to solve a problem, and there is also no right or wrong to discuss here, nor do I see the need for that. What I put here is just a suggestion to follow good practices, as is laid out in Psrs, if you prefer to express opposition to this, you are free to do so.

  • It has nothing to do with. Ivan.. it is unproductive to stay here arguing and you do not seem to interpret right what I put and even "half" that distorting the sense of what I said... Anyway, as far as I was concerned, the matter was already closed. I’m not one to ignore someone leaving an argument open, but I can’t talk to you, because you distort what I put on.. I’m sorry, I’m sorry, I’m sorry..

  • Let’s go continue this discussion in chat.

Show 6 more comments

Browser other questions tagged php mysql mysqli

You are not signed in. Login or sign up in order to post.