MySQLi Prepared Statements - Insert Value in database table
By using Prepared statements, you reduce the parsing time and in Prepared statements we use bind param that minimize bandwidth to the server.
In Prepared statements, Same SQL statements is executed with high efficiency.
Prepared statements execution basically consists two stages :
- First prepare the query for execution
- Second execute the prepared statement
You can prevent SQL Injection Attacks by using prepared statments.
Now question is how you can assure that attacker will not able to change the intent of a query even if SQL queries are passed by an attacker.
Suppose if any attacker want to fetch your user data by some smart input such as WHERE 1=1 that return always true.
But with prepared statements, the parameterized query would not be vulnerable and it will look for a username which literally matched with passing string '1'='1.
- //Insert Value in database table using Prepared Statements in MySQLi
- $product_name = 'ExpertPHP';
- $product_price = '60000';
- $product_details = 'Provide Online Tutorials';
- $query = "INSERT INTO products (product_name, product_price, product_details) VALUES(?, ?, ?)";
- $stmt = $db->prepare($query);
- //There are four types of parameters to bind for markers, where (s = string, i = integer, d = double, b = blob)
- $stmt->bind_param('sss', $product_name, $product_price, $product_details);
- if($stmt->execute()){
- print 'Successfully inserted and Last inserted ID is : ' .$stmt->insert_id .'<br />';
- }else{
- die('Error : ('. $db->errno .') '. $db->error);
- }
- $stmt->close();
Here in this example, i use simply bind_param method to bind a parameter.
You notice that i have 3 parameters with similar type to bind then i use sss and if you have various type of parameter such as : name and height then you can define sd and if you have only one parameters such as : name then you can define only s.
Now in if condition, i execute statement by using $stmt->execute() method if above query execute successfully then it will give you last inserted id.
