If you liked what you've learned so far, dive in!
Subscribe to get access to this tutorial plus
video, code and script downloads.
Our app would be a little more fun to play with if we had some more interesting pets in our pet table. So I made a file with some SQL queries that insert some new furry friends. Open up the resources/episode3/pets.sql file:
TRUNCATE TABLE `pet`;
INSERT INTO `pet`
(`id`, `name`, `breed`, `age`, `weight`, `bio`, `image`)
VALUES
(1, 'Chew Barka', 'Bichon', '2 years', 8, 'The park, The pool ...', 'pet1.png');
Cool. TRUNCATE is the mysql keyword you use when you want to empty a table. It’s handy when you’re developing - but try not to use this on the production database, you’d probably end up in the dog house. After that, we just have a bunch of INSERT queries. Copy the contents. Now open up PHPMyAdmin, click the SQL tab and paste it in there. And voila! We’ve got 4 pets.
Tip
You can also execute all the queries in this file from the command line:
mysql -u root -p air_pup < resources/episode3/pets.sql
When we refresh the homepage, we see our 4 pets. In fact, if we had 1 million pets in our table, they would all load on this page. That would be way more dog food than I can afford!
Instead, no matter how big this table gets, let’s only show 3 pets on the homepage. To do this, we can change our query to tell MySQL to only return 3 rows. This is done by adding a LIMIT at the end:
// lib/functions.php
function get_pets()
{
// ...
$result = $pdo->query('SELECT * FROM pet LIMIT 3');
$pets = $result->fetchAll();
return $pets;
}
When we refresh now, only 3 pets! Hey, that’s one more little MySQL trick. But get_pets() is kind of broken now - it always returns only 3 results. So our contact page says that we only have 3 pets. That’s not true!
Instead of hardcoding 3 in the query, let’s add a $limit argument to the function:
// lib/functions.php
function get_pets($limit)
{
// ...
}
Now we can use this to dynamically create the query string. Technically, this is simple. But wait! What I’m about to show you is a huge security hole, I mean huge! Take the $limit variable and add it to the end of the string. This is called concatenation:
// lib/functions.php
function get_pets($limit)
{
// ...
// THIS IS A HUGE SECURITY FLAW - TODO - WE WILL FIX THIS!
$result = $pdo->query('SELECT * FROM pet LIMIT '.$limit);
// ...
}
Now, open up index.php and pass 3 as an argument to get_pets():
// index.php
require 'lib/functions.php';
$pets = get_pets(3);
Now refresh. We still have 3 pets!
But don’t celebrate - we have a big security bug! Whenever you create a database query that has some variable part to it, you open yourself up to an SQL injection attack. This is probably the most common attack on the web and one mess-up will give an attacker full access to do anything to your databse.
I’ll explain this attack more in a few minutes, and we’ll close this security hole.
hey Jay,
You can download all the resources related to this course on any course video page - see the "Download" button in the top right corner of this page above the video player. If the links are not active for you - that means the course is paid and to download the resources you either need to buy the specific course or buy a SymfonyCasts subscription first.
If you have trouble accessing resources of courses you paid for - feel free to contact us directly via our Contact form describing your problem: https://symfonycasts.com/contact
Cheers!
Hey Jay,
Awesome! Thanks for confirming it works for you now :)
If you ever have any questions - don't hesitate to ask ;)
Cheers!
Where can I download the complete resources files?