1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'PHP Tutorials' started by Matt, Oct 23, 2017.

  1. Matt

    Matt <?= "PHP Developer" ?> Staff Member

    236
    211
    153
    Credits:
    515
    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:

    [​IMG]

    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:

    [​IMG]
    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:
    [​IMG]
    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.
    [​IMG]
    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.
    [​IMG]
    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:
    [​IMG]



    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: Oct 24, 2017
    SCP, CabCon and Autistic like this.
  2. Harry

    Harry I'm 'Serious'ly a better Developer Premium Member

    949
    742
    278
    Credits:
    3,847
    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 likes this.
  3. Matt

    Matt <?= "PHP Developer" ?> Staff Member

    236
    211
    153
    Credits:
    515

    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.
     
  4. Harry

    Harry I'm 'Serious'ly a better Developer Premium Member

    949
    742
    278
    Credits:
    3,847
    Our IT teacher just copy & pastes from W3Schools :tearsofjoy:
     
  5. Matt

    Matt <?= "PHP Developer" ?> Staff Member

    236
    211
    153
    Credits:
    515
    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.
     
    Harry likes this.
  6. SCP

    SCP Moderator Staff Member Donator

    352
    361
    73
    Credits:
    2,665
    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.
     
    RosticGamer99 and CabCon like this.
  7. Matt

    Matt <?= "PHP Developer" ?> Staff Member

    236
    211
    153
    Credits:
    515
    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.
     

Share This Page