This content originally appeared on DEV Community 👩‍💻👨‍💻 and was authored by Mateusz Jasiński
Introduction
In my last post I mentioned prepared statements.
Now, let's expand on this topic and see how we can implement them in our code
Here, I will show prepared statements in PDO and mysqli.
I strongly recommend to use PDO. I'll explain why a little later.
In this post I expect that You know basic terms connected to databases (Query, DBMS etc.) and basic usage of PHP (variables, functions, objects), so I won't cover those topic completely in this post
Database
I'm going to use simple database, that looks like this
Typical way of executing queries - query() function
Most common and often taught method of executing queries is with query() function
ex.
$sql = 'SELECT * FROM `users` WHERE `username` = "'.$username.'"';
$db = new mysqli("localhost","root", "", "test");
$res = $db->query($sql);
print_r($res->fetch_assoc());
This code is quite bad, as it would be vulnerable to SQL injection attack, if the $username variable is not defined by us, but by the user
Of course we can validate $username (using htmlentities for example)
then we would increase queries safety, but let's go further
Our first prepared statement
Ok, now we know something about running queries in PHP, let's discover another way called prepared statements.
What is a prepared statement? It's PHP's feature to run similar (or not really) queries in much safer way than using other methods.
So, let's take a look at this example, and analyze it line by line
$sql = 'SELECT * FROM `users` WHERE `username` = ?';
$db = new mysqli("localhost","root", "", "test");
$stmt = $db->prepare($sql);
$stmt->bind_param("s", $username);
$stmt->execute();
$res = $stmt->get_result();
print_r($res->fetch_assoc());
- First two lines should look quite familiar, but we have a difference in query. instead of
"'.$username.'"we put question mark. Why? Let's see In the next line we are actually declaring, that here we use prepared statement.
-
The 5th line contains the answer for our question from first point. Why did we use a ? instead of typical variable insertion?
Because we are binding these values to query, and not preparing complete query. Let's analyze arguments- First: Type - It's just info for PHP what will be type of passed variable, whether it will be
s(string),i(int) etc. - Second: Variable - This is an actual variable "inserted" into the query. For each of these, there should be additional letter in first argument ex.
- First: Type - It's just info for PHP what will be type of passed variable, whether it will be
$stmt->bind_param("is",$id, $username);
- 6th line calls execute() method, which runs our query
- Then in 7th line we get our statements results, but it's not really a result. if we run
print_r($res), we will see something like this
mysqli_result Object ( [current_field] => 0 [field_count] => 4 [lengths] => [num_rows] => 1 [type] => 0 )
So let's check the last line
- 8th line is getting our actual results, here in form of associative array, like this
Array ( [id] => 2 [username] => username1 [pass] => pass1 [email] => email1 )
But we can also use other forms of results, such as fetch_array(), fetch_object() and more.
That's it. Now you know, how to write prepared statements in mysqli, so let's focus on other method
PDO Introduction
What is PDO?
Basically speaking, it's pre-installed php extension used as another method of connecting to database.
It's biggest advantage over mysqli is that PDO works on most of popular DBMS's, without necessity to change existing PHP code (SQL code can be different between some databases)
In PDO prepared statements are recomended method of running queries.
Prepared statements in PDO
Prepared statements in PDO are slightly different than those in mysqli
First of all, connecting to database is not the same as in mysqli
In PDO we use dsn instead of host name and database name.
Our dsn looks like this
mysql:host=localhost;dbname=test
And opening connection with database is started with creation of PDO object
$db = new PDO("mysql:host=localhost;dbname=test","root", "");
Now, what changes in PDOs prepared statemens?
We don't need bind_param (but still it is, so we can use bindParam instead), when we can pass our parameters as arguments to execute() function
$stmt->execute([$username]);
NOTE: This argument has to be an array, so if you're trying to use some values that are not an array, convert them into it (you can use just [] like me there)
And last thing, fetching data instead of bunch of functions we have bunch of PDOs constants like:
- PDO::FETCH_ASSOC
- PDO::FETCH_OBJ and some more
So, our PDO prepared statement finally looks like this
$sql = 'SELECT * FROM `users` WHERE `username` = ?';
$db = new PDO("mysql:host=localhost;dbname=test","root", "");
$stmt = $db->prepare($sql);
$stmt->execute([$username]);
print_r($stmt->fetch(PDO::FETCH_SERIALIZE));
Conclusion
After reading this post you know
- What are prepared statements
- What do they look like
- How to use them
- What is PDO, and why is it useful
That's it. I hope you enjoyed reading. You can leave some feedback in the comments, I'd appreciate that.
Thanks for reading and see you in the next post
This content originally appeared on DEV Community 👩‍💻👨‍💻 and was authored by Mateusz Jasiński
Mateusz Jasiński | Sciencx (2023-01-05T22:14:20+00:00) Quick guide for PHP prepared statements. Retrieved from https://www.scien.cx/2023/01/05/quick-guide-for-php-prepared-statements/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.