Learn

Although we can use PHP to send SQL queries to PostgreSQL, sometimes SQL queries can be dangerous.

For example, how would we write a query that lets a user get a book’s details by providing its ID?

// Get the ID from the frontend
$id = $_POST['id'];

// Like this?
$booksQuery = $db->query('SELECT * FROM books WHERE id = ' . $id);

But what if instead of entering a number, a malicious user enters 1 or 1 = 1?

Then the database will run this query:

$booksQuery = $db->query('SELECT * FROM books WHERE id = 1 or 1 = 1');

And since 1 = 1 is always true, the database will return every row from the books table. While returning all books might not be a big problem, an attacker can use the same technique to return a list of all users, passwords, and other confidential information!

SQL injection happens when a malicious user provides a SQL command instead of data, and the database executes it. We can prevent SQL injection by telling the database which values should be treated only as data.

We do that with prepared statements.

A prepared statement is a pre-defined template containing SQL and optionally placeholders. We use placeholders to tell the database where to place the data we will provide when executing the statement.

We create prepared statements using the $db object’s prepare() method. And instead of appending the $id variable, we use the placeholder :id .

// Get the ID from the frontend
$id = $_POST['id'];

// Create a prepared statement
$bookQuery = $db->prepare('SELECT * FROM books WHERE id = :id');

The next step is to run the execute() method, and pass in an array with a key-value pair which maps our placeholders to variables.

// Map :id to $id
$bookQuery->execute(['id' => $id]);

The final step is to fetch the result:

$book = $bookQuery->fetch(PDO::FETCH_ASSOC);

Instructions

Let’s practice converting a vulnerable dynamic statement to a safer prepared statement. Re-write the statement on line 8 so it’s safe from SQL injection attacks.

Although prepared statements improve your security posture, malicious hackers are always looking for ways to defeat protections. So let’s go one step further by sanitizing $id with the filter_input() function to ensure we’re only passing in numbers.

Next, write a SELECT statement in line 22 to return all books written by the $author provided and assign the result to the variable $books.

Next, add a new book to the books table with an INSERT statement in line 33. Use the $title, $author, and $year provided.

For the remaining checkpoints, there is no need to use the fetch() method since we won’t be checking return data.

Then, update a book in your database with an UPDATE statement. Find the book authored by the $author, and change the title and year to the new values provided.

Finally, delete a book from the database using a DELETE statement with the $id provided.

Take this course for free

By signing up for Codecademy, you agree to Codecademy's Terms of Service & Privacy Policy.

Or sign up using:

Already have an account?