Prepared Statements in PHP and MySQL is a process where we prepare our database for a particular query and later we send our parameters to execute our query to our database. The advantage it offers is the same query can be executed many times without sending the whole query, all we need to change is paramaters.
INSERT INTO USERS (name,username) values (?,?)
Here we prepare our database to insert into users and later we assign values we need to provide to execute our sql query. ‘?’ are the parameters.
Prepared statements basically work like this:
- Prepare: An SQL statement template is created and sent to the database. Certain values are left unspecified, called parameters (labeled “?”). Example: INSERT INTO users VALUES(?, ?, ?)
- The database parses, compiles, and performs query optimization on the SQL statement template, and stores the result without executing it
- Execute: At a later time, the application binds the values to the parameters, and the database executes the statement. The application may execute the statement as many times as it wants with different values
Normal mysqli or mysql statement are prone to sql injections, anyone can enter any malicious script to delete a table or even wipe out our whole database.!! . So Prepared statements are very useful against SQL injections, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.
A Sample to insert data into database with prepared statement.
$conn = mysqli_connect('host','username','password','database'); $statement = $conn->preapre("insert into users (username,email,password) values (?,?,?)"); $statement->bind_param('sss',$username,$email,$password); // here 's' represents string $statement->execute(); $statement->close(); $conn->close();