SQL INjection

Posted on 2011-05-05
Last Modified: 2012-05-11
I am starting to read info from a file is there with php then printing out the results. I intended to place these results in my MySQL database and the return the results with a query. I am curious this may be a stupid question but is it possible to have an SQL injection when preforming this function. If so how likely and what are step that can be taken to prevent it.
Question by:nbham29
    LVL 38

    Expert Comment

    by:Aaron Tomosky
    If the file is provided by the user then yes. All user input Should be thought of as an attack vector.
    LVL 16

    Expert Comment

    by:Kalpesh Chhatrala

    Author Comment

    would I be able to use the mysqli_real_escape_string() when reading a file
    LVL 38

    Expert Comment

    by:Aaron Tomosky
    No. You can only use that with an open db connection.

    Author Comment

    so if I take information from a csv file and want to put it into a database will I be able to use the above mentioned mysqli_real_escape_String() before inputting the file information into my database
    LVL 38

    Expert Comment

    by:Aaron Tomosky
    Yes. The rough outline should be something like this:
    Load the file using filegetcontents, explode on lines, exPlode on fields, if you have number fields check for numbers, etc... Try to validate as much as possible. If you don't need HTML then you can striphtmlchars at the beginning. Then you create the db connection, build the query wrapping your variables in mysqlrealescapestring. Execute, check for errors, return success, close connection.
    LVL 5

    Expert Comment

    yes. it is possible.

    check this:
    LVL 107

    Accepted Solution

    Make a Google search for PHP security and read the top ten listings in detail.  This is a moving target.  In the USA there are entire college majors devoted to the subject.  If you are new to PHP this book will help.

    Best regards, ~Ray
    LVL 9

    Expert Comment

    SQL Injection: What is it?
    SQL Injection is one of the many web attack mechanisms used by hackers to steal data from organizations. It is perhaps one of the most common application layer attack techniques used today. It is the type of attack that takes advantage of improper coding of your web applications that allows hacker to inject SQL commands into say a login form to allow them to gain access to the data held within your database.
    In essence, SQL Injection arises because the fields available for user input allow SQL statements to pass through and query the database directly.

    SQL Injection: An In-depth Explanation
    Web applications allow legitimate website visitors to submit and retrieve data to/from a database over the Internet using their preferred web browser. Databases are central to modern websites – they store data needed for websites to deliver specific content to visitors and render information to customers, suppliers, employees and a host of stakeholders. User credentials, financial and payment information, company statistics may all be resident within a database and accessed by legitimate users through off-the-shelf and custom web applications. Web applications and databases allow you to regularly run your business.

    SQL Injection is the hacking technique which attempts to pass SQL commands (statements) through a web application for execution by the backend database. If not sanitized properly, web applications may result in SQL Injection attacks that allow hackers to view information from the database and/or even wipe it out.

    Such features as login pages, support and product request forms, feedback forms, search pages, shopping carts and the general delivery of dynamic content, shape modern websites and provide businesses with the means necessary to communicate with prospects and customers. These website features are all examples of web applications which may be either purchased off-the-shelf or developed as bespoke programs.

    These website features are all susceptible to SQL Injection attacks which arise because the fields available for user input allow SQL statements to pass through and query the database directly.

    SQL Injection: A Simple Example
    Take a simple login page where a legitimate user would enter his username and password combination to enter a secure area to view his personal details or upload his comments in a forum.

    When the legitimate user submits his details, an SQL query is generated from these details and submitted to the database for verification. If valid, the user is allowed access. In other words, the web application that controls the login page will communicate with the database through a series of planned commands so as to verify the username and password combination. On verification, the legitimate user is granted appropriate access.

    Through SQL Injection, the hacker may input specifically crafted SQL commands with the intent of bypassing the login form barrier and seeing what lies behind it. This is only possible if the inputs are not properly sanitised (i.e., made invulnerable) and sent directly with the SQL query to the database. SQL Injection vulnerabilities provide the means for a hacker to communicate directly to the database.

    The technologies vulnerable to this attack are dynamic script languages including ASP, ASP.NET, PHP, JSP, and CGI. All an attacker needs to perform an SQL Injection hacking attack is a web browser, knowledge of SQL queries and creative guess work to important table and field names.
    The sheer simplicity of SQL Injection has fuelled its popularity.
    Other contents:

    Why is it possible to pass SQL Queries to the database even though this is hidden behind a firewall?
    Is my database at risk to SQL Injection?
    What is the impact of SQL Injection?
    Example of a SQL Injection Attack
    How do I prevent SQL Injection attacks?
    Why is it possible to pass SQL queries directly to a database that is hidden behind a firewall and any other security mechanism?

    Firewalls and similar intrusion detection mechanisms provide little or no defense against full-scale SQL Injection web attacks.

    Since your website needs to be public, security mechanisms will allow public web traffic to communicate with your web application/s (generally over port 80/443). The web application has open access to the database in order to return (update) the requested (changed) information.
    In SQL Injection, the hacker uses SQL queries and creativity to get to the database of sensitive corporate data through the web application.

    SQL or Structured Query Language is the computer language that allows you to store, manipulate, and retrieve data stored in a relational database (or a collection of tables which organise and structure data).
    SQL is, in fact, the only way that a web application (and users) can interact with the database. Examples of relational databases include Oracle, Microsoft Access, MS SQL Server, MySQL, and Filemaker Pro, all of which use SQL as their basic building blocks.
    SQL commands include SELECT, INSERT, DELETE and DROP TABLE. DROP TABLE is as ominous as it sounds and in fact will eliminate the table with a particular name.

    In the legitimate scenario of the login page example above, the SQL commands planned for the web application may look like the following:
    SELECT count(*)
    FROM users_list_table
    AND password=’FIELD_PASSWORD”
    In plain English, this SQL command (from the web application) instructs the database to match the username and password input by the legitimate user to the combination it has already stored.
    Each type of web application is hard coded with specific SQL queries that it will execute when performing its legitimate functions and communicating with the database. If any input field of the web application is not properly sanitised, a hacker may inject additional SQL commands that broaden the range of SQL commands the web application will execute, thus going beyond the original intended design and function.

    A hacker will thus have a clear channel of communication (or, in layman terms, a tunnel) to the database irrespective of all the intrusion detection systems and network security equipment installed before the physical database server.

    Is my database at risk to SQL Injection?
    SQL Injection is one of the most common application layer attacks currently being used on the Internet. Despite the fact that it is relatively easy to protect against SQL Injection, there are a large number of web applications that remain vulnerable.

    According to the Web Application Security Consortium (WASC) 9% of the total hacking incidents reported in the media until 27th July 2006 were due to SQL Injection. More recent data from our own research shows that about 50% of the websites we have scanned this year are susceptible to

    SQL Injection vulnerabilities.
    It may be difficult to answer the question whether your web site and web applications are vulnerable to SQL Injection especially if you are not a programmer or you are not the person who has coded your web applications.

    Our experience leads us to believe that there is a significant chance that your data is already at risk from SQL Injection.

    Whether an attacker is able to see the data stored on the database or not, really depends on how your website is coded to display the results of the queries sent. What is certain is that the attacker will be able to execute arbitrary SQL Commands on the vulnerable system, either to compromise it or else to obtain information.

    If improperly coded, then you run the risk of having your customer and company data compromised.
    What an attacker gains access to also depends on the level of security set by the database. The database could be set to restrict to certain commands only. A read access normally is enabled for use by web application back ends.

    Even if an attacker is not able to modify the system, he would still be able to read valuable information.

    What is the impact of SQL Injection?
    Once an attacker realizes that a system is vulnerable to SQL Injection, he is able to inject SQL Query / Commands through an input form field.
    This is equivalent to handing the attacker your database and allowing him to execute any SQL command including DROP TABLE to the database!

    An attacker may execute arbitrary SQL statements on the vulnerable system. This may compromise the integrity of your database and/or expose sensitive information. Depending on the back-end database in use, SQL injection vulnerabilities lead to varying levels of data/system access for the attacker. It may be possible to manipulate existing queries, to UNION (used to select related information from two tables) arbitrary data, use subselects, or append additional queries.

    In some cases, it may be possible to read in or write out to files, or to execute shell commands on the underlying operating system. Certain SQL Servers such as Microsoft SQL Server contain stored and extended procedures (database server functions). If an attacker can obtain access to these procedures, it could spell disaster.

    Unfortunately the impact of SQL Injection is only uncovered when the theft is discovered. Data is being unwittingly stolen through various hack attacks all the time. The more expert of hackers rarely get caught.

    Example of a SQLInjection Attack
    Here is a sample basic HTML form with two inputs, login and password.

    <form method=”post” action=””>
    <input name=”tfUName” type=”text” id=”tfUName”>
    <input name=”tfUPass” type=”password” id=”tfUPass”>

    The easiest way for the login.asp to work is by building a database query that looks like this:
    SELECT id
    FROM logins
    WHERE username = ‘$username’
    AND password = ‘$password’

    If the variables $username and $password are requested directly from the user’s input, this can easily be compromised. Suppose that we gave “Joe”
    as a username and that the following string was provided as a password:
    anything’ OR ‘x’='x
    SELECT id
    FROM logins
    WHERE username = ‘Joe’
    AND password = ‘anything’ OR ‘x’='x’

    As the inputs of the web application are not properly sanitised, the use of the single quotes has turned the WHERE SQL command into a two-component clause.
    The ‘x’='x’ part guarantees to be true regardless of what the first part contains.
    This will allow the attacker to bypass the login form without actually knowing a valid username / password combination!

    How do I prevent SQL Injection attacks?
    Firewalls and similar intrusion detection mechanisms provide little defense against full-scale web attacks. Since your website needs to be public, security mechanisms will allow public web traffic to communicate with your databases servers through web applications. Isn’t this what they have been designed to do?

    Patching your servers, databases, programming languages and operating systems is critical but will in no way the best way to prevent SQL Injection Attacks.

    How to check for SQL injection vulnerabilities Securing your website and web applications from SQL Injection involves a three-part process:
    1. Analysing the present state of security present by performing a thorough audit of your website and web applications for SQL Injection and other hacking vulnerabilities.
    2. Making sure that you use coding best practice santising your web applications and all other components of your IT infrastructure.
    3. Regularly performing a web security audit after each change and addition to your web components.

    Furthermore, the principles you need to keep in mind when checking for SQL Injection and all other hacking techniques are the following: “Which parts of a website we thought are secure are open to hack attacks?” and “what data can we throw at an application to cause it to perform something it shouldn’t do?”.

    Checking for SQL Injection vulnerabilities involves auditing your website and web applications. Manual vulnerability auditing is complex and very time-consuming. It also demands a high-level of expertise and the ability to keep track of considerable volumes of code and of all the latest tricks of the hacker’s ‘trade’.

    The best way to check whether your web site and applications are vulnerable to SQL injection attacks is by using an automated and heuristic web vulnerability scanner.

    An automated web vulnerability scanner crawls your entire website and should automatically check for vulnerabilities to SQL Injection attacks.
    It will indicate which URLs/scripts are vulnerable to SQL injection so that you can immediately fix the code. Besides SQL injection vulnerabilities a web application scanner will also check for Cross site scripting and other web vulnerabilities.

    Signature-Matching versus Heuristic Scanning for SQL Injection Whereas many organisations understand the need for automating and regularising web auditing, few appreciate the necessity of scanning both off-the-shelf AND bespoke web applications. The general misconception is these custom web applications are not vulnerable to hacking attacks.
    This arises more out of the “it can never happen to me” phenomenon and the confidence website owners place in their developers.

    A search on Google News returned 240 matches on the keyword “SQL Injection” (at time of writing) since the 14th October. Secunia and SecuObs report dozens of vulnerabilities of known web applications on a daily basis. Yet, examples of hacked custom applications are rarely cited in the media. This is because it is only the known organisations (e.g. Choicepoint, AT&T, PayPal) that hit the headlines over the past few months.

    It is critical to understand that custom web applications are probably the most vulnerable and definitely attract the greatest number of hackers simply because they know that such applications do not pass through the rigorous testing and quality assurance processes of off-the-shelf ones.

    This means that scanning a custom web application with only a signature-based scanner will not pinpoint vulnerabilities to SQL Injection and any other hacking techniques.
    Establishing and testing against a database of signatures of vulnerabilities for known applications is not enough. This is passive auditing because it will only cover off-the-shelf applications and any vulnerabilities to new hacking techniques will not be discovered. In addition, signature matching would do little when a hacker launches an SQL Injection attack on your custom web applications. Hack attacks are not based on signature file testing – hackers understand that known applications, systems and servers are being updated and secured constantly and consistently by respective vendors. It is custom applications that are the proverbial honey pot.

    It is only a handful of products that deploy rigorous and heuristic technologies to identify the real threats. True automated web vulnerability scanning almost entirely depends on (a) how well your site is crawled to establish its structure and various components and links, and (b) on the ability of the scanner to leverage intelligently the various hacking methods and techniques against your web applications.

    It would be useless to detect the known vulnerabilities of known applications alone. A significant degree of heuristics is involved in detecting vulnerabilities since hackers are extremely creative and launch their attacks against bespoke web applications to create maximum impact.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    This is a general how to create your own custom plugin system for your PHP application that you designed (or wish to extend a third party program to have plugin functionality that doesn't have it yet).  This is not how to make plugins for existing s…
    Introduction Many web sites contain image galleries; a common design for these galleries includes a page with a collection of thumbnail images.  You can click on each of the thumbnail images to see the larger version of the image.  This is easily i…
    Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
    The viewer will learn how to count occurrences of each item in an array.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now