Matt
Web Developer
- Messages
- 245
- Reaction score
- 215
- Points
- 828
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.
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:
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.