Prevent SQL injection attacks with prepared statements

DISCLAIMER: This post may contain “affiliate” links to products and services I recommend. I’ll receive a small commission if you decide to purchase one of these products or services. I only recommend products I genuinely believe will help you in running your freelance business.

Bit of a hot topic lately in my inbox.

Here’s what an old, vulnerable query might look like:

$expected_data = 1;
$query = "SELECT * FROM users where id=$expected_data";
$result = $mysqli->query($query);

The problem here is we’re injecting user-submitted data directly into our SQL statement without any sort of escaping or validation. So, a hacker could enter something like this in our form:

1; DROP TABLE users;

Changing our full query to:

SELECT * FROM users where id=1; DROP TABLE users;

Which, as you can probably see, will execute the SELECT statement but then drop our users table. No bueno. And, that’s a simple example. SQL injection attacks can be used to do all sorts of things: getting passwords, gaining privileges, making superusers… and all sorts of stuff.

Luckily, there’s an easy way to prevent this class of SQL injection:

Prepared statements.

Prepared statements split the query from the data so that the data submitted can’t be used to alter how the query is run; thus preventing injection attacks. Here’s an example of how our code would change:

$expected_data = 1;
$stmt = $mysqli->prepare("SELECT * FROM users where id=?");
$stmt->bind_param("d", $expected_data);
$stmt->execute();
$result = $stmt->get_result();

Notice how we separated the data from the query. We send the query to the server first and then we bind the data to that. This prevents the submitted data from altering the query and letting the hacker in.

This is how you should write your queries.

Now, if this stuff is new to you or you don’t feel like you quite fully get it yet, then I recommend diving into it. One good way to learn everything PHP is with this tutorial. You’ll learn prepared statements… but also OOP, all of MySQL with PHP, $_GET, $_POST, working with files, folders, if/else/switch… all of it. Plus, you build several fully-functioning scripts so you learn how to put it all together and build something real.

No brainer, IMNSHO: http://johnmorrisonline.com/php

Later,

John “Don’t Hack Me Bro” Morris

You might also like

Do you want more freelance clients?

I’ll show you what I learned over the last 15 years to grind out (from absolute scratch) a backlog of new clients wanting to hire you. Who your best client prospect are, what services you should be offering them, where to find them and more. Just enter your email address in the box below and let’s get started:

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn
Share on reddit
Reddit
Share on pinterest
Pinterest
John Morris

JOHN MORRIS

I’m a 15-year veteran of freelance web development. I’ve worked with bestselling authors and average Joe’s next door. These days, I focus on helping other freelancers build their freelance business and their lifestyles.

Do you want more freelance clients?

Enter your email below to get started building your system for consistently bringing in new freelance clients:

WHAT OTHERS ARE SAYING

Xan Barksdale

Xan Barksdale

Very professional worker who is extremely knowledgable in WordPress and Wishlist Member. I would definitely hire again.

Bradley Smith

John and I have worked together on numerous projects. John is very quick and efficient and was a pleasure to work with.

Ray Edwards

I recommend John every chance I get. If every person I worked with were as committed to excellence, punctuality, value, and unquestionable integrity… the world would be a better place. Highest recommendation.

Lori Grant

John did an outstanding job on my project. I highly recommend him and look forward to working with him on future projects.

Chris Aitken

He significantly improved my site through his expert knowledge of PHP, CSS and Javascript. Would definitely recommend John to others.

Sukh Plaha

John is a fantastic and patient tutor, who is not just able to share knowledge and communicate it very effectively – but able to support one in applying it. However, I believe that John has a very rare ability to go further than just imparting knowledge and showing one how to apply it. He is able to innately provoke one’s curiosity when explaining and demonstrating concepts, to the extent that one can explore and unravel their own learning journey. Thanks very much John!

Daniel Mohlendick

On the Freelancing on Upwork course: “This is by far the best course i have watched on Skillshare!! Thank you so much.”

Close Menu