Prepared Statements and using them with PHP and MySQL

Hi guys! This is my first story on medium and I’m excited to share about the concept of prepared statements and why should we use them.

Why to use prepared statements ?

Well, The use of SQL databases, unfortunately, make websites or APIs vulnerable to SQL Injection attacks. There are many negative consequences to this attack and may cause a lot of damage to the website.

Hence the most important reason to use a prepared statement is to keep our website safe from SQL Injection. I will discuss how it will prevent SQLi attacks in an upcoming article, but for now, just stick with the point that “Prepared statements will prevent SQLi attacks”.

The second reason is to increase efficiency. Yes, efficiency! consider sometimes you might want to execute the same query multiple times but with different query parameters. Example. “SELECT * FROM user WHERE uid = 1234” and second time “SELECT * FROM user WHERE uid = 8124”. You will understand this after knowing how it is implemented.

How to use prepared statements ? (An overview)

The implementation of prepared statements follows the following steps.

1. Write the query statement.
2. Prepare the statement using mysqli_prepare() function.
3. Bind the values to prepared statement using mysqli_stmt_bind_param() function.
4. Finally execute query using mysqli_stmt_execute()
5. At the step 4, query will be executed, if you want to fetch the result you can furthur use mysqli_stmt_get_result() function.

Please note that if-else statements will be used to handle the error.

How to use prepared statements? (Example 1 — Using with INSERT query)

The following code shows the implementation. You can use the above steps to understand how the below code works.

How to use prepared statements? (Example 2— Using with SELECT query)

If you understood the above code, then follow up is just an extension for that. And it goes as follows:

Note:

mysqli_stmt_bind_param() function expects binding parameters to be passed by reference parameters. For e.g. mysqli_stmt_bind_param($stmt, "s", "XYZ") will throw an error.

In an upcoming story, I will be posting how these prepared statements help us in preventing SQLi attacks. I hope you understood how you can implement prepared statements quickly with PHP and MySQL. This was my first article and I tried my level best to keep mistakes out of the article, I regret if there were any mistake and let me know them in response.

Thanks for reading and I hope it was helpful to you. Have a great day.