Back to Basics in PHP: Escaping SQL Queries

Disclaimer of Liability: This article is for information purposes only and should not be used in making, or refraining from making, any decision. We cannot guarantee  or warrant that the information, advice, facts or suggestions published in this article are accurate, correct, valid, up to date, timely or reliable; and your use or reliance on the information, advice, facts or suggestions in this article are at your sole risk and responsibility.

 

It is vital to stress the importance of ensuring input that is to be checked, added or removed from an SQL database is properly escaped in order to ensure that malicious users do not use simple SQL comment syntax in order to comment out of the contained SQL query to be able to execute arbitrary SQL code on your database.

How does this work?

It’s very simple. The  characters are used to comment out SQL code after it. Take this example:

SELECT * FROM users WHERE username = 'admin' --

What’s happening here? MySQL will ignore the rest of the SQL query after ‘admin’. Because, in theory, a “username” of “admin” will exist in the database in this scenario. So the user could in theory log in as the admin user at this point. In this scenario, the text the user inputted into the text field would have been admin’–

How can I avoid SQL injections?

To avoid SQL injections, you need to properly escape any input before adding them to an SQL query. For example, if the variable $username contains the data from a POSTed form with a username text field, you can ensure the input is safe to be placed in an SQL query using the mysql_real_escape_string() function. You can also use other PHP functions too, including addslashes(), which similarly protects against SQL injections. And if you want to remove the slashes added by the addslashes() function in PHP, you can use the stripslashes() function.

Furthermore, if you’re using PHP Data Objects for database interactivity, you still need to properly escape SQL queries (you may not know about PHP Data Objects are if you do not understand object-oriented programming design principles). If you use PHP Data Objects, the query() method does not escape queries for you, and expects you to do it. Hence, in this cases, it is important you use the quote() method of the PDO class, in order to ensure input is escaped before being added to an SQL query.

How does it work?

Because malicious end-users only need to add an arbitrary single- or double-quotation to the input field on a web page to execute arbitrary SQL code in scenarios where input is not properly escaped, the many PHP functions simply prevent this from occurring by either adding slashes to the start and end of the string after the postback has occurred (in other words, once the user submits the form and the data is sent to the script for processing, etc.) or alternatively by backslashing quotes in the string, as what the addslashes() function does, which theoretically prevents the SQL query from being escaped out of by adding arbitrary single- or double-quotes to an input field.

What are the implications of SQL injections?

There are many technical and legal implications from having insecure code that causes SQL injections. First and foremost, you can cause personal information to be visible to a malicious end-user. But also concerning is what the malicious user can do – the malicious user can cause inconsistency in the database data by deleting or modifying data in the database or by simply dropping entire tables; which can be just as catastrophic.

Even if you are not a business, you can still be held liable under UK law concerning data protection if the cause of any kind of security breach that results in personal information being accessible, copied, stolen and/or deleted is the result of bad, ineffective or inadequate security provisions being in place to safeguard and protect personal information as required under the Data Protection Act 1998.

Back to Basics in PHP: Escaping SQL Queries
Written by: AFRIDA YANTI
Reting: 4.9


Cheap Best Hosting is a leading provider of web hosting, shared hosting, resellers hosting, virtual private servers, vps hosting, dedicated servers and domain name registration.


Follow me on Google+ | Twitter | Facebook | Linkedin | YouTube

Share This Post

Related Articles

Leave a Reply

© 2017 Cheap Best Hosting Blog. All rights reserved. · Entries RSS · Comments RSS
Hosting By Cheap Best Hosting