Prepared Statements in PHP and MySQL is a feature that is used to execute a same query repeatedly with different parameters with more efficiency and security. In this todays world prepared statements are widely usedand is totally different from normal MySQLi queries as in prepared statements a template or a query is sent to a database with a parameter ‘?’ which prepares the database for the same query and later on we define our parameter and execute it.
Working Of Prepared Statements
- First we set our query and prepare the database for the same without any certain/specific values. It is like telling the database “I will enter some data in the table users and I will give info later on”.
- The database now parses and compile the query and store the result without executing it.
- Finally we provide/define the parameters and execute the query. An the same query my be executed as many times we want by providing/defining parameters.
- Bandwidth Saver.
insert into users (username,email,password) values (?,?,?)
How It Is Secure.
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.
Here I have a sample PHP script to brief Out.
$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();
The argument may be one of four types:
- i – integer
- d – double
- s – string
- b – BLOB
We must have one of these for each parameter. By telling mysql what type of data to expect, we minimize the risk of SQL injections.