In the mysql CLI I have prepared a statement like this:
PREPARE registrarUser FROM 'INSERT INTO Users (Users_name,Email,pass) values (?,?,?)'
In my database the prepared statements have to be done this way,instead of using a php method like this::
$conn->prepare("INSERT INTO Users (Users_name,Email,pass) VALUES (?, ?, ?)");
So I can't use the prepared statement or bind arguments.
I have tried this query which mimics the required statements in mysql CLI
$query = sprintf('
SET @Users_name = "%s";
SET @Email= "%s";
SET @pass = "%s";
EXECUTE registrarUser USING @Users_name, @Email, @pass;',$Users_name,$Email,$pass);
But it returns the following syntax error:
Errormessage: 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 'SET @Email= "eds@gmail.com"; SET @pass = "Thinkshap2"; EXECUTE registrar' at line 2
Does anyone know if there is a way to do it? Thank you very much in advance;
It's not possible.
From the MySQL manual
The scope of a prepared statement is the session within which it is created...
- A prepared statement created in one session is not available to other sessions.
No, it's not possible. Prepared statements have the session scope. Whenever you open a new connection in PHP, you open a new MySQL session. You can use PREPARE and EXECUTE in PHP, but both operations have to be done using the same session.
In other words, statements created with PREPARE do not persist on the database server. They only exist for the lifetime of the current session.
The reason why you are getting a syntax error in PHP is because you have concatenated multiple SQL statements together. You can't do that by default in PHP due to security considerations. Execute each one separately. For example, this works:
$stmt = $mysqli->query("PREPARE registrarUser FROM 'SELECT ?'");
$stmt = $mysqli->query("EXECUTE registrarUser USING 32");
Warning. Using PREPARE and EXECUTE from PHP defeats the main purpose of prepared statements usage in PHP. The main advantage is that you can separate variables from SQL syntax. You can't do that with PREPARE and EXECUTE. This is why both PDO and mysqli have prepared statements. Use mysqli::prepare() and mysqli_stmt::execute()