CabConModding
Facebook
Twitter
youtube
Discord
Contact us
RSS
Menu
CabConModding
Home
New
Top
Premium
Rules
FAQ - Frequently Asked Questions
Games
Fornite
Call of Duty: Black Ops 3
Clash of Clans
Grand Theft Auto 5
Apex Legends
Assassin’s Creed Origins
Forums
Premium
Latest posts
What's new
Latest posts
New profile posts
Latest activity
Members
Current visitors
New profile posts
Log in
Register
What's new
Premium
Latest posts
Menu
Log in
Register
Navigation
Install the app
Install
More options
Dark Theme
Contact us
Close Menu
Forums
Tech Boards
Web Development
PHP
PHP Tutorials
PHP/SQL - PLEASE! Use Prepared statements and Parameterized Queries
JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser
.
Reply to thread
Message
<blockquote data-quote="Matt" data-source="post: 29393" data-attributes="member: 8"><p>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.</p><p></p><p><span style="font-size: 18px"><strong>What is SQL Injection?</strong></span></p><p>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.</p><p></p><p>SQL Code will generally take form a little bit like this:</p><p></p><p><img src="https://image.prntscr.com/image/N94mVzfrRYWpfkUvNYOzfQ.png" alt="" class="fr-fic fr-dii fr-draggable " style="" /></p><p></p><p>In English, this would Translate to:</p><p></p><p>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.</p><p></p><p>Your variables are generally based off of posted information from a user-input, such as a Login-field.</p><p></p><p>All good? No.</p><p></p><p>A malicious user, could simply right SQL in to one of your variables, for example.</p><p></p><p>$matt= "); DROP TABLES *; --</p><p></p><p>The use is now writing malicious SQL Queries in to your variables, which will be posted to your Database. So it will now read.</p><p></p><p>Open the newsletter_subscribers table, get the values ready. Now drop all tables and finish.</p><p></p><p>This will allow the user to delete your whole database. A rather comical example of this is Little Bobby Tables:</p><p></p><p><img src="https://imgs.xkcd.com/comics/exploits_of_a_mom.png" alt="" class="fr-fic fr-dii fr-draggable " style="" /></p><p>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</p><p></p><p><span style="font-size: 18px"><strong>So how do I go about doing that?</strong></span></p><p></p><p>It's simple really. Instead of connecting to your database via MySQL, use PDO.</p><p></p><p>To initiate the PDO connection, write the following code.</p><p>1) Define your connection variables, this is optional but I like to do it:</p><p><img src="https://image.prntscr.com/image/yxHSt_sRS3iU0C6KbQpduQ.png" alt="" class="fr-fic fr-dii fr-draggable " style="" /></p><p>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.</p><p><img src="https://image.prntscr.com/image/xdLbNuYOR42N2T3FYqEGIw.png" alt="" class="fr-fic fr-dii fr-draggable " style="" /></p><p>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.</p><p>$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);</p><p>$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);</p><p>3) Prepare your statement.</p><p><img src="https://image.prntscr.com/image/HaJEiO8aQYG83p5HXaC60Q.png" alt="" class="fr-fic fr-dii fr-draggable " style="" /></p><p>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:</p><p><img src="https://image.prntscr.com/image/-kq28jW9SiSNpvFp9N1QLA.png" alt="" class="fr-fic fr-dii fr-draggable " style="" /></p><p></p><p></p><p></p><p>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.</p><p></p><p><strong><span style="font-size: 18px">So how/why does this stop SQL Injection?</span></strong></p><p>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. </p><p></p><p></p><p></p><p>Note: There is another way to do this using MySQLi, however I find the PDO method the easiest to explain.</p><p></p><p>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.</p><p></p><p>Edit #2 21/10/17 - Explained how this prevents SSQL injection from occuring.</p></blockquote><p></p>
[QUOTE="Matt, post: 29393, member: 8"] 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. [SIZE=5][B]What is SQL Injection?[/B][/SIZE] 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]https://image.prntscr.com/image/N94mVzfrRYWpfkUvNYOzfQ.png[/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]https://imgs.xkcd.com/comics/exploits_of_a_mom.png[/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 [SIZE=5][B]So how do I go about doing that?[/B][/SIZE] 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]https://image.prntscr.com/image/yxHSt_sRS3iU0C6KbQpduQ.png[/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]https://image.prntscr.com/image/xdLbNuYOR42N2T3FYqEGIw.png[/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]https://image.prntscr.com/image/HaJEiO8aQYG83p5HXaC60Q.png[/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]https://image.prntscr.com/image/-kq28jW9SiSNpvFp9N1QLA.png[/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. [B][SIZE=5]So how/why does this stop SQL Injection?[/SIZE][/B] 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. [/QUOTE]
Verification
Post reply
Forums
Tech Boards
Web Development
PHP
PHP Tutorials
PHP/SQL - PLEASE! Use Prepared statements and Parameterized Queries
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.
Accept
Learn more…
Top