SymfonyCasts stands united with the people of Ukraine
If you liked what you've learned so far, dive in!
Subscribe to get access to this tutorial plus
video, code and script downloads.
Both get_pets and get_pet contain an SQL query where one part of it is a variable. Whenever you have this situation, you’re opening yourself up for an SQL injection attack. Want to see one in action?
Change the id value in the URL of your browser to a very specific string:
Hmm, so things look ok. But refresh again. The pet is gone! In fact, check out the database in PHPMyAdmin - the pet table is empty! Seriously, we just emptied the entire pet table by playing with the URL!
Let’s dump the query in get_pet and refresh:
function get_pet($id)
{
$pdo = get_connection();
$query = 'SELECT * FROM pet WHERE id = '.$id;
var_dump($query);die;
$result = $pdo->query($query);
return $result->fetch();
}
This is an SQL injection attack:
SELECT * FROM pet WHERE id = 4;TRUNCATE pet
By cleverly changing the URL, our code is now sending 2 queries to the database: one to select some data and another to empty our table. If our site were in production, imagine all the dogs that would never get walked and cats that would never get fed salmon. All because someone injected some SQL to drop all of our data and put us out of business. These attacks can also be used to silently steal data instead of destroying it.
Fortunately, we can get our data back by re-running the queries from resources/episode3/pets.sql in PHPMyAdmin.
How do we save our site? With prepared statements. Don’t worry, it’s an unfriendly term for a simple idea. Prepared statements let us build a query where the variable parts are kept separate from the rest of the query. By doing this, MySQL is able to make sure that the variable parts don’t include any nasty SQL code.
To use prepared statements, our code needs a little rework. First, change the query function to prepare and put a :idVal where the id value should go. This returns a PDOStatement object, so let’s rename the variable too.
Next, call bindParam to tell MySQL that when you say :idVal, you really mean the value of the $id variable. To actually run the query, call execute():
// lib/functions.php
// ...
function get_pet($id)
{
$pdo = get_connection();
$query = 'SELECT * FROM pet WHERE id = :idVal';
$stmt = $pdo->prepare($query);
$stmt->bindParam('idVal', $id);
$stmt->execute();
return $stmt->fetch();
}
It’s a bit longer, but it works! We built the query, but replaced the dynamic part with a placeholder: a word that starts with a colon. This isn’t any PHP magic, it’s just how prepared statements work. The prepare() function does not actually execute a query, it just returns a PDOStatement object that’s ready to go. To actually make the query, we call execute, but not before calling bindParam for each placeholder with the real value. To get the data from the query, we finish up by calling either fetch() or fetchAll().
This is actually how I recommend you write all your queries, whether you have any variable parts of not.
Let’s repeat the change in get_pets(). Just write the query with the LIMIT placeholder, bind the parameter, call execute() to make the query and fetchAll() to get the data back. Simple!
// lib/functions.php
function get_pets($limit = null)
{
$pdo = get_connection();
$query = 'SELECT * FROM pet';
if ($limit) {
$query = $query .' LIMIT :resultLimit';
}
$stmt = $pdo->prepare($query);
if ($limit) {
$stmt->bindParam('resultLimit', $limit);
}
$stmt->execute();
$pets = $stmt->fetchAll();
return $pets;
}
Refresh the homepage to make sure it’s working. Oh crap, it’s not! But there’s also no error.
Whenever there’s a problem with a query, we can configure PHP to tell us about it, instead of staying silent like it is now. Find get_connection() and add one extra line to configure our PDO object:
function get_connection()
{
// ...
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $pdo;
}
Refresh now! Ah, a nice error! So this is what it looks like if your query has an error. The problem is actually subtle and really not that important. Pass a third argument to bindParam:
$stmt->bindParam('resultLimit', $limit, PDO::PARAM_INT);
Now it works. This tells MySQL that this value is an integer, not a string. This almost never matters, but it does with LIMIT statements. So like I said before, don’t give this too much thought.
When you do have errors with a query, the best way to debug is to try the query out first in PHPMyAdmin, then move it to PHP when you have it perfect.
Ok team, we are killing it. In just a few short chapters, we’ve updated almost our entire application to use a database. The only part that doesn’t use the database is the new pet form, which we’ll fix early in the next episode.
Use your new-found power for good: create some tables in PHPMyAdmin and start querying for data. Don’t forget to put up your fence to protect against SQL Injection attacks, so that the adorable poodle, fluffy Truncate Table, doesn’t cause you problems.
Seeya next time!
Hey Nicholas,
You shouldn't, but you're definitely ready for the OOP course after this PHP track ;) If you are not sure in your strong OOP skills (Classes, Abstract classes, Interfaces, Traits and other OO concepts), then choose the OOP track first, otherwise choose whatever you like more.
Cheers!
Hey Nicholas
The OOP will be fine, really! It holds a lot of OO concepts which use in Symfony or any modern framework/library. But first of all, take a look at a few short courses if you don't yet:
https://knpuniversity.com/s...
https://knpuniversity.com/s...
And then you can choose our new course https://knpuniversity.com/s... if you love JavaScript a lot or you can switch to learn Symfony. ;)
Cheers!
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
That line breaks my code. Makes localhost unable to load the page (localhost is currently unable to handle this request.). If I take that line out it all works good. Any ideas?
EDIT: if I change 'PDO::ERRMODE_EXCEPTION' to 'PDO::ERRMODE_WARNING' the code works (but I still don't get the error printing).
Hey Luke W.!
Good job finding that the change to WARNING helps. It also gives me a hint :). Here's what I think is happening:
1) One of your queries is actually failing. Before you put PDO::ERRMODE_EXCEPTION, the error doesn't hurt anything. After you put this, PDO is throwing an exception, which is killing your page.
2) But then why do we see a nice error page in the tutorial but you just see an empty, broken page? It's probably because you need to tweak 2 settings in your php.ini file:
display_errors = On
error_reporting = E_ALL
Assuming you can find your php.ini file (try running php --ini at the command line), change these 2 lines (make sure they don't have a ; at the start of the line - that comments the line out - remove that if it is there) and then restart your web server.
Let us know if that helps!
Cheers!
You're a champion Ryan, that fixed it! For reference incase anyone else using MAMP on a Mac struggles with this:
The correct php.ini file to edit is in: /Applications/MAMP/bin/php/phpX.X.X (there should be a folder for the version you're using, 7.4.2 in my case)/conf/php.ini
Thanks again dude, and thank you for creating some of the best PHP/Symf tutorials on the web.
I just completed the 3 php courses. I am very satisfied with the quality even though the coding challenges stopped at some point. I just wanted to say thanks, your information is presented very nicey and I enjoied the pet puns :)
Hey Kyle!
Thank you for such good words about our courses, you made our day! :)
About challenges, what problem did you hit? Did you able to complete them finally? Please, let us know if you still have any problems with them and we will help!
Cheers!
I see it's been a while since this episode was finished. Is there enough demand for episode 4 at this point, or should I just move on to other tracks?
Hey Lucio S.!
As you probably guessed, we don't have any hard plans currently. We covered most of the really fundamental things that we wanted to cover, but of course not everything. And while we *do* want to have a really robust PHP track to get anyone started, most of our users come for the Symfony tutorials.
But, to help things along, what types of things (if any) were you hoping to see in an episode 4? We do keep track of user comments and requests - it definitely helps drive what we do and when.
Cheers!
weaverryan thanks for getting back to me. I guess I'm left wondering what my next steps should be. I have a very firm understanding of HTML, CSS, UX/UI, and design, but have never fully taken the dive into programming. The flow of your tutorials is really nice, and helped me understand the basics better than any other I've attempted. Episode 3 left me hanging with "...which we’ll fix early in the next episode...Seeya next time!" It makes me question where I should go next?
Hey Lucio Eastman!
Ah! Then I think I can answer that :). If you want to keep going on this serious programming dive, go here: https://symfonycasts.com/tr...
Yes, there is more to learn about PHP / programming in general, but you're probably ready to step up to some object-oriented stuff. This will really open some doors, because so much of the backend technology is written with OO code. We don't have coding challenges on everything (sorry!) but we do have them on part 3 and part 4 of our OO track.
Oh, OR, you could go a more JavaScript route: https://symfonycasts.com/tr.... In that track, we *do* assume you already have a grasp on JavaScript (so if you don't think you fit into that, there are other sites that do a great job with, but we don't cover it), then helps really show the principles and tools of modern JavaScript.
And if you need any more guidance, let us know!
Cheers!
Hey, thank you very much :). As you saw in the other thread, we don't have a timeline yet - but definitely user demand plays into how we prioritize things. BUT, I did just create a new tutorial about this in the "planning" stages - you can subscribe to get an update when the tutorial comes out: http://knpuniversity.com/sc... I hope that helps! You might also enjoy the object-oriented tutorials if you haven't checked those out yet.
Cheers!
Hi there!
Not sure how well this still holds up, but in a current PHP 8.1 environment, the last bit of the tutorial doesn't work and I get an error:
<blockquote>"Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in C:\xampp\htdocs\studying\start\lib\functions.php on line 28"</blockquote>
I fixed this by adding another wrapping another if-statement around the bindParam line:
If there is no if-statement, it tries to bind the parameter to the initial query which is 'SELECT * FROM pet', but since there's no :resultLimit variable to bind to, it just breaks? Since it seems to have worked at some point in your tutorial, I assume this broke with a newer version of PHP? Or have I overlooked something or made another mistake somewhere else? If that's the case, is my solution correct or is it just a workaround / messy?
Thanks!
(Also there's no 4th course coming I assume :P)