What are prepared statements in PHP?
The prepared statement is a special function embedded in PHP which allows programmers to write codes that can be executed multiple times in an efficient manner by our database. When communicating with your database, extra care is given to security, query time, and server request size. With prepared statements, these factors are considered.
Importance of prepared statements
- Whenever a query is parsed with an already prepared statement, the time required to do this is reduced because it skips the preparation stage and goes straight into the execution stage. This can happen more than once.
- SQL injection attacks are reduced with prepared statements even when data is improperly validated or when cleaned form data is present. This is because the
input template Which defines the data type to be accepted - When such codes are executed, the whole query is sent only at the first instance. On subsequent execution, only the bound parameters are sent. This goes a long way in conserving the bandwidth of the server.
How PHP MySQLi prepared statements work
1. Preparation
-
First, there is an initial preparation of the SQL template.
-
Second, we initialize the preparation using the
mysqli_stmt_init()function which passes an argument to the database connection.
2. Parsing and compiling
- Using the
mysqli_stmt_prepare()function, the database connection is passed as the first argument. - As the second argument, the SQL query is passed and the statement is parsed, compiled, and optimized without execution by the database.
3. Execution
-
Whenever the values are provided, they are bound to the template variables and the database executes the command.
-
This is achieved when the
mysqli_stmt_bind_param()and themysqli_stmt_execute()functions pass the relevant parameters.
4. Getting results and closing our statement
- The result of the execution can be grabbed using the
mysqli_stmt_get_result()function to pass the initialization variable as an argument. - We can close it all using the
mysqli_stmt_get_result()function with the initialization variable as an argument.
Examples
In our examples, we have created:
- A database called
myDatabasewith＄dbconas the connection variable. - A table called
myTable, which has columns with the namesemailandusernamefrom which we would make a selection of rows. - Your table should have some dummy data inside it as well.
With that setup we can get started.
Prepared statement in PHP MySQLi procedural
<?php$nameOfServer = "localhost";$serverUsername = "username";$password = "password";$databaseName = "myDatabase";// Create connection$dbcon = mysqli_connect($nameOfServer, $serverUsername, $password, $databaseName);// Check connectionif (!$dbcon) {die("Connection failed: " . mysqli_connect_error());}//prepare sql template using "?" as placeholders.$query = "SELECT * FROM myTable WHERE username = ? OR email = ?; ";// initialize database connection.$bindstmt = mysqli_stmt_init($dbcon);//prepare the query and check if successfulif(!mysqli_stmt_prepare($bindstmt,$query)){echo "stmt failed";exit();}//Now bin the parameters if preparation was a success.mysqli_stmt_bind_param($bindstmt,'ss',$username, $email);//execute the statement.mysqli_stmt_execute($bindstmt);//get the result and fetch it as an array$stmtresult = mysqli_stmt_get_result($bindstmt);if($row = mysqli_fetch_assoc($stmtresult)){return $row;}else{$result = false;return $result;}//Now close the statement and db connectionmysqli_stmt_close($bindstmt);mysqli_close($dbcon);}?>
Prepared statement in PHP MySQLi object-oriented programming format
In this example, the assumptions and declared variables will be used, but we will do an insertion rather than a selection.
<?php$nameOfServer = "localhost";$serverUsername = "username";$password = "password";$databaseName = "myDatabase";// Create connection$dbcon = new mysqli($nameOfServer, $serverUsername, $password, $databaseName);// Check connectionif ($conn->connect_error) {die("Connection failed: " . $conn->connect_error);}// prepare and bind$stmt = $conn->prepare("INSERT INTO myTable (fullname, username, email) VALUES (?, ?, ?)");$stmt->bind_param("sss", $fullname, $username, $email);// set parameters and execute$fullname = "mary john";$username = "marrywise";$email = "mary@wise.com";$stmt->execute();//this will insert the values into our table/*if the value to be inserted was from a formyou would have to replace our static values herewith the values of the form input if it is set .*/$stmt->close();$dbcon->close();;?>
