Prevent SQL injection attacks with prepared statements

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

Chicks dig this s***

Saw this scrub on Twitter post this namby-pamby bullshizen: === I will NEVER be impressed by your money, car, looks, social status or job title.

Read More »
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.

The simple secrets to high-paying freelance clients

What makes clients willing to pay $5,000, $10,000 even $20,000 and up for your services? Download and install my mobile app and I’ll show you. It’s free. Just click the button below:

Clients Like:

Inc. Magazine Logo
Lewis Howes Logo
Ray Edwards Logo

WHAT OTHERS ARE SAYING

Bob Patterson

Not only is John a very talented programmer and developer, he is also an excellent communicator. He has a talent for taking complex subjects and communicating them in terms that anyone can understand. This is a rare combination. This ability has enabled me to take my skills and knowledge to the next level. Thank you John for for all that you do.

Michael Skye

Michael Skye

John is a man of integrity, who gives generously of himself to projects and people he cares about.

Jason Rumley

Jason Rumley

John has a habit of over-delivering on the expectations he sets up. That’s why he’s the best.

Lewis Howes

John is amazing at building membership sites. He converted one of my sites over from it’s existing (hardly working) platform over to the clean and simple to use WishList membership platform. I highly recommend using John and WishList for any of your membership site needs.

Aaron Gott

Aaron Gott

John has a particular knack for the development and training of others.

Thabo Motsoahae

John is one of the best instructors I have come across, I learned a lot from his online tutorials.

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.

Steve Dimmick

Steve Dimmick

John has provided expert knowledge and advice on multiple occasions that have helped me better serve my clients. John is a Rockstar!

Daniel Mohlendick

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

Jim DeJonge

Jim DeJonge

John has a relaxed and engaging manner. His advice is solid and the explanations are well thought out.

Chris Aitken

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

Andrew Malone

Andrew Malone

John Morris is exceptional in his ability to give focused insight into Freelancing and starting one’s business. His direct methods inspire confidence in his honesty.