PHP Prepared Statements
PHP Prepared Statements used to avoid SQL injections. In this tutorial, I explain how to implement a prepared statement in PHP.
Steps for Implement Prepared statement in PHP
- Make a connection with the database server
- Initialize all prepared statements
- Initialize all query templates
- Prepare all statements
- Assign all bind parameters
- Execute
- Close the prepared statements
- Done
Database Connection(config.php)
|
1 2 3 4 5 6 7 |
<?php $dbuser="root"; $dbpass=""; $host="localhost"; $dbname = "test"; $mysqli = new mysqli($host, $dbuser, $dbpass, $dbname); ?> |
Structure of the user table
|
1 2 3 4 5 6 7 8 |
CREATE TABLE IF NOT EXISTS `user` ( `id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `email` varchar(255) NOT NULL, `contactno` int(11) NOT NULL, `addrss` longtext NOT NULL, `posting_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; |
Now Create a HTML Form for Data Insertion(index.php)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
<form name="stmt" method="post"> <table> <tr> <td>Name :</td> <td><input type="text" name="name" required="required" /> </td> </tr> <tr> <td>Email :</td> <td><input type="email" name="email" required="required" /></td> </tr> <tr> <td>Contact no. :</td> <td><input type="text" name="contact" required="required" /></td> </tr> <tr> <td>Address :</td> <td><textarea name="addrss" cols="30" rows="4" required="required"></textarea></td> </tr> <tr> <td></td> <td><input type="submit" name="submit" value="Submit" /></td> </tr> </table> </form> |
Code For Insert Data Into Database Using PHP Prepared Statement. Put this code on the top of the index.php page
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<?php include('config.php'); if(isset($_POST['submit'])) { $name=$_POST['name']; $email=$_POST['email']; $contact=$_POST['contact']; $addrss=$_POST['addrss']; $ad="insert into user(name,email,contactno,addrss) values(?,?,?,?)"; $stmt= $mysqli->prepare($ad); $stmt->bind_param(ssis,$name,$email,$contact,$addrss); $stmt->execute(); $stmt->close(); echo "<script>alert('Data added Successfully');</script>" ; } ?> |
Store the query in a variable.
Prepares a statement returning a result set as aPrepared Statement.
We can use question marks (?) for values.
we can then call the execute(array()) method.
Binding Datatypes
bind_params is the array of the parameters you want to bind.
Types: s = string, i = integer, d = double, b = blob
execute() :-Execute the prepared statement. We can use an array of values to replace the question mark parameters.
close() :- Close the prepared statements.
Here is the full code that we have written during this tutorial:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
<?php include('config.php'); if(isset($_POST['submit'])) { $name=$_POST['name']; $email=$_POST['email']; $contact=$_POST['contact']; $addrss=$_POST['addrss']; $ad="insert into user(name,email,contactno,addrss) values(?,?,?,?)"; $stmt= $mysqli->prepare($ad); $stmt->bind_param(ssis,$name,$email,$contact,$addrss); $stmt->execute(); $stmt->close(); echo "<script>alert('Data added Successfully');</script>" ; } ?> <html> <title>Prepared statement</title> <body> <h2>Insert Data in the Database using PHP Prepared Statement</h2> <form name="stmt" method="post"> <table> <tr> <td>Name :</td> <td><input type="text" name="name" required="required" /> </td> </tr> <tr> <td>Email :</td> <td><input type="email" name="email" required="required" /></td> </tr> <tr> <td>Contact no. :</td> <td><input type="text" name="contact" required="required" /></td> </tr> <tr> <td>Address :</td> <td><textarea name="addrss" cols="30" rows="4" required="required"></textarea></td> </tr> <tr> <td></td> <td><input type="submit" name="submit" value="Submit" /></td> </tr> </table> </form> </body> </html> |
For run, this code on localhost create a database with name test and import the SQL file available inside the download package.
