How to use sprintf to create a query with date_format()

Question

I am reformulating a system of a client and the same is using procedural mode in login and not PDO, but to give more security, I used the sprintf, but it’s not working. Look:

$sql = sprintf(
    "SELECT *, DATE_FORMAT(DataAcesso,'%d/%m/%Y') AS DataDeAcesso, 
    DATE_FORMAT(DataAcesso,'%H:%i') AS HoraDeAcesso 
    FROM loja_admin WHERE EmailAdmin = '%s' AND SenhaAdmin = '%s'",
    mysqli_real_escape_string($this->conexao, $loginUsuario), 
    mysqli_real_escape_string($this->conexao, $codificado)
);

$query = mysqli_query($this->conexao, $sql);

And when I give one echo in the variable $sql, nothing appears. But when I take the DATE_FORMAT(), works. I would have some way to solve this?

Answer 1

I’m reworking a client system and the same is using the procedural mode in the login and not PDO, but to give more security, I used sprintf, but it’s not working.

The use or not of the sprintf(), PDO or use procedural mode mysqli_* does not change at all the relationship with the security of the code.

What really ensures the security of the code is you escape the parameters of your query with the mysqli_real_escape_string() or else use Prepared Statments, which are also available in the PDO, but can also be used in the extension of the mysqli in procedural form

$statment = mysqli_prepare(
    $this->conexao, 
    "SELECT *, DATE_FORMAT(DataAcesso,'%d/%m/%Y') AS DataDeAcesso, 
    DATE_FORMAT(DataAcesso,'%H:%i') AS HoraDeAcesso 
    FROM loja_admin WHERE EmailAdmin = ? 
    AND SenhaAdmin = ?"
);

mysqli_stmt_bind_param($stmt, 'ss', $loginUsuario, $codificado);
mysqli_stmt_execute($stmt);

Review the documentation of these methods for more details.

mysqli_prepare

mysqli_stmt_bind_param

mysqli_stmt_execute

Answer 2

I managed to solve escaping the character %. Look at:

$sql = sprintf(
    "SELECT *, DATE_FORMAT(DataAcesso,'%%d/%%m/%%Y') AS DataDeAcesso, 
    DATE_FORMAT(DataAcesso,'%%H:%%i') AS HoraDeAcesso 
    FROM loja_admin WHERE EmailAdmin = '%s' AND SenhaAdmin = '%s'",
    mysqli_real_escape_string($this->conexao, $loginUsuario), 
    mysqli_real_escape_string($this->conexao, $codificado)
);