PHP/SQL - PLEASE! Use Prepared statements and Parameterized Queries

Matt

Backend Web Developer
Messages
244
Points
603
Honestly some people will never learn. Since this is becoming a place where a lot of people who are transitioning from the Modding community to the Web design community come to seek information, I thought it would only be right to post something like this here. Plus, it's 2017 and people are still relying on the Deprecated Original MySQL API instead of the MySQLi or PDO.

What is SQL Injection?
SQL Injection is, in essence, forcing (potentially)malicious code in to your database Queries. It doesn't take a genius to realise that this is not a good thing. If you don't understand what I'm trying to say, take this example.

SQL Code will generally take form a little bit like this:



In English, this would Translate to:

Open the newsletter_subscribers table and prepare the select fields. Insert the values that are stored in the Variables, matt, last_name, email in to the correct specified columns.

Your variables are generally based off of posted information from a user-input, such as a Login-field.

All good? No.

A malicious user, could simply right SQL in to one of your variables, for example.

$matt= "); DROP TABLES *; --

The use is now writing malicious SQL Queries in to your variables, which will be posted to your Database. So it will now read.

Open the newsletter_subscribers table, get the values ready. Now drop all tables and finish.

This will allow the user to delete your whole database. A rather comical example of this is Little Bobby Tables:


Not only can you delete the database tables, you can bypass logins. Which would leave your site vulnerable, as malicious users would be able to give themselves access to the websites CMS/Admin Panel, if the queries are not prepared and inputs santizied

So how do I go about doing that?

It's simple really. Instead of connecting to your database via MySQL, use PDO.

To initiate the PDO connection, write the following code.
1) Define your connection variables, this is optional but I like to do it:

2) Use these details to initiate the PDO connection, of course you can name the variable whatever you want, but dbh seems to be the common example.

2.5) Write this code to stop your script throwing a fatal error, if this occurs you can now see the error that has been thrown using PDOExceptions.
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
3) Prepare your statement.

Notice how our values are now :first_name, :last_name, :email. These are just the parameters we are binding our Posted variable values to. As you can see in the line below, we are calling the bindParam function to achieve this. This, in essence, is how you stop all first-hand SQL-Injections from occuring. Your last step is to just get your posted values, this should be done before you attempt to bind your parameters, so just place this further up in your code:




And that, in my opinion. Is the poorest way that I could have explained Parameterized Queries and Prepared Statements. If you have any questions feel free to ask. I'll probably revisit this when I get the time.

So how/why does this stop SQL Injection?
Well it's rather simple really. The Query and the data are sent separately to your Database. This means that the system already knows the SQL that it's going to be executing (hence $dbh>PREPARE), making it impossible for people to plant malicious code.



Note: There is another way to do this using MySQLi, however I find the PDO method the easiest to explain.

Edit: I am also aware of the poor-grammar whilst I was commenting my code, it was wrote in the early hours of the morning when I realised that my Newsletter was previously vulnerable.

Edit #2 21/10/17 - Explained how this prevents SSQL injection from occuring.
 
Last edited:

Harry

Software Engineer
Premium Member
Messages
1,272
Points
748
Learnt how to do this using W3Schools in the car (using WAMP) whilst I had 5 hours left and I've forgotten how to do it the mysqli_query(); way xD

Prepared makes more sense to me now :smile:

Take the time and learn it!
 

Matt

Backend Web Developer
Messages
244
Points
603
Learnt how to do this using W3Schools in the car (using WAMP) whilst I had 5 hours left and I've forgotten how to do it the mysqli_query(); way xD

Prepared makes more sense to me now :grinning:

Take the time and learn it!

I’m proud if this made any sense to you; I haven’t really explained how the method stops SQL Injection, I just know that it does.

W3S Is what, unfortunately, is the Standard over in the UK. I’ve finished my certification for them and it was patronisingly easy.
 

Harry

Software Engineer
Premium Member
Messages
1,272
Points
748
I’m proud if this made any sense to you; I haven’t really explained how the method stops SQL Injection, I just know that it does.

W3S Is what, unfortunately, is the Standard over in the UK. I’ve finished my certification for them and it was patronisingly easy.
Our IT teacher just copy & pastes from W3Schools :tearsofjoy:
 

Matt

Backend Web Developer
Messages
244
Points
603
Our IT teacher just copy & pastes from W3Schools :tearsofjoy:
Sadly my former IT teacher was too wound up in the fact that he needed this job because he had £50k of Student debt to pay rather than trying to teach us.

Oh but when he did he also copied from W3-Schools.
 

SCP

Moderator
Staff member
Donator
Messages
391
Points
423
I see MySQL so many times. Why they don't use PDO or MySQLi?

Anyways, thanks for the good tutorial @Matt! :y: Maybe do you think you could create another thread with a small template with writing and reading from a database connected over PDO? I think many people would help this a lot if they could build their applications on a good template.
 

Matt

Backend Web Developer
Messages
244
Points
603
I see MySQL so many times. Why they don't use PDO or MySQLi?

Anyways, thanks for the good tutorial @Matt! :y: Maybe do you think you could create another thread with a small template with writing and reading from a database connected over PDO? I think many people would help this a lot if they could build their applications on a good template.
Thanks for the feedback :smile:

People probably use it because it’s the first thing W3 advocate on their Database tutorials, people are to naïve to research and discover that this isn’t good practice.

Good idea, I’ll write up a few templates with real world examples when I get home from work today.
 

Similar threads


Top