SQL Injection Attack: What is it, and how to prevent it.

SQL Injection Attack: What is it, and how to prevent it.

Summary: The way that Yahoo! was hacked, SQL Injection attack, is the same method as many other hacks in the news recently: SQL Injection. SQL Injection attacks are common for the following reasons: • The prevalence of SQL Injection vulnerabilities • Databases are attractive targets because they typically contain critical application information • The approach (i.e., the hack) is not new and is well documented on dozens of forums

SHARE:

The way that Yahoo! was hacked, SQL Injection attack, is the same method as many other hacks in the news recently: SQL Injection. SQL Injection attacks are common for the following reasons:
     •    The prevalence of SQL Injection vulnerabilities
     •    Databases are attractive targets because they typically contain critical application information
     •    The approach (i.e., the hack) is not new and is well documented on dozens of forums

While Tech Crunch reported that many users were employing ridiculously weak passwords such as “12345” and “password”, having a strong password, as I wrote about recently in Fighting back against Anonymous, LulzSec and the global cyber insurgency, is worthless if the site servers or applications are not configured or patched appropriately.

So, what is a SQL Injection attack?

The point of an SQL Injection attack is to compromise a database, which is an organized collection of data and supporting data structures. The data can include user names, passwords, text, etc.

Structured Query Language  is the programming language used to manage data in a database; more appropriately, a relational database management systems (RDBMS). The types of management systems that employ Structured Query Language include Microsoft SQL Database, Oracle, MySQL, PostgreSQL, and others.

A simple example to get basic table name information would be the following:

Select * from table_name :

This statement uses a wildcard (*) to return the contents of the table. The hack could also include inserting information into the database, like a new user for the purposes of doing bad things.

insert into users(username,userid) values("HackerBob","hb123");

The point of the hack is not just to get information from the target site. Depending on the intention of the malicious hooligans attacking you, it can include to bypass logins, to access data as in the Yahoo! case, to modify the content of a website as when hackers replace the website with a new front page, or simply shutting down the server. Often it is a combination of the above.

Step one of the attack is to scan sited to see if a vulnerability exists. Believe it or not, a hackers best friend is Google. Employing Google Dork, a hacker is able to search for vulnerabilities using Google tricks. After a site is identified a hacker will attempt to gain a foothold and search for files containing usernames and directories that are known to contain sensitive data.

The attack is opportunistic and does not take a lot of research or a large team to pull off. Infact, you can go to Google directly and enter one of the following commands as illustrated in a Ethical Hacking Tutorial online:

     •    inurl:index.php?id=
     •    inurl:gallery.php?id=
     •    inurl:article.php?id=
     •    inurl:pageid=

From the listing of sites that Google returns, you will then need to checked each site for vulnerabilities.

www.TargetSite.com + inurl:index.php?id=

In fact the first site that comes back when you run inurl:article.php?id= in a Google browser is also the topic of discussion on a hacker site HackeForums.net. The discussion starts:

“http://www.targetsite.org/article.php?id=129. SQL The website is listed as having only one column, so I run into problems when I try to...” The forum allows for collaboration between hackers. Yikes!

I found an email for the targeted site and contacted them regarding the vulnerability. Hopefully, they will forward the email to someone who can take appropriate action.

The good news here is that these attacks are very simple to prevent or avoid. The Open Web Application Security Project has a SQL Injection Prevention Cheat Sheet, which outlined primary and additional defenses.

The primary defenses that are used to fight include,
     •    Prepared Statements (Parameterized Queries) - Parameterized queries force developers to define all the SQL code, then pass in each parameter to the query, which allows the database to distinguish between code and data, regardless of what input is supplied.
     •    Stored Procedures - a stored procedure is defined and stored in the database itself, and then called from the application rather than something that a user is allowed to enter.
     •    Escaping all User Supplied Input - Each DBMS supports one or more character escaping schemes specific to certain kinds of queries. If you then escape all user supplied input using the proper escaping scheme for the database you are using, the DBMS will not confuse that input with SQL code written by the developer, thus avoiding any possible SQL injection vulnerabilities.

Additional Defenses include
     •    Least Privilege – or minimizing the privileges assigned to every database account, so that users have enough permission to do their job, but no more.
     •    White List Input Validation - Input validation is used to detect unauthorized input before it is processed by the application, thereby preventing the attack

Have you experienced an SQL Injection hack at your organization? How did your organization combat the attack? Let me know.

Topics: Security, Data Management, Microsoft, Open Source, Oracle

Gery Menegaz

About Gery Menegaz

Gery Menegaz is a Chief Architect for IBM with more than 20 years supporting technologies in the financial, medical, pharmaceutical, insurance, legal and education sectors. My Full-Time Employer is IBM. I write as a freelancer for ZDNet.

Kick off your day with ZDNet's daily email newsletter. It's the freshest tech news and opinion, served hot. Get it.

Talkback

20 comments
Log in or register to join the discussion
  • Oracle Database Firewall

    Sorry, Gery. I know you are from IBM, :D
    yoroto
    • :)

      I am a single biker woman. My friend told me about___seekingbikers.com___she told me it is the best place for bikers to find Friendship, Love, and Romance! I have tried, it is fantastic, hundreds of thousands of hot biker men and biker babes are there. Come in and give it a shot, you will find your biker match to share the passion for motorbikes! :)
      ChristinaSpears
      • Christina Spears are cheating on Loverock Davidson?

        Shame on you..............
        Over and Out
        • As well as Ferrell and Johnny Vegas.

          n/t
          droidfromsd
  • Also Worth Avoiding PHP

    PHP has entirely different sets of built-in functions for accessing different DBMSes (MySQL versus PostgreSQL versus whatever). This means you have to write one set of database calls for MySQL, another for PostgreSQL, and so on. This kind of thing gets tedious and repetitive, and of course mistakes tend to creep in. Other, more rationally-designed languages, like Python and Perl, provide a uniform DBMS-independent interface, to which all the DBMS-specific back ends conform. That lets you write a single common set of DBMS-independent code.

    In short, use a language that saves you work, and you will write better code.

    Oh, and XKCD made fun of this in its "Bobby Tables" cartoon years ago.
    ldo17
    • XKCD

      I've got that one Bookmarked. :D
      lehnerus2000
    • Clearly you haven't actually used PHP recently...

      Oh please stop spreading this lie around the Internet already. PHP 5.3 and later have PDO built-in, which is PHP's generic way of accessing multiple databases with a single interface. Under older versions of PHP (5.2 and earlier), there is PEAR DB, DB2, and MDB which all effectively do the same thing - merge the various APIs into a coherent library of functions.
      leonadams
  • Database security

    Although most SQL-DBMSs provide comprehensive security facilities, it is surprising how many web applications still use a single login for all users.

    This is a fundamental security flaw. If a user uses SQL injection techniques they can see and manipulate all data in the database. If database security is used properly then a particular user can only do what they could do anyway through the application.

    I've even seen occasions where the single user used for web access was the system administrator - which means SQL injection could effectively drop the entire database.

    Many web developers reject using DBMS security, but they are neglecting a powerful tool.

    Of course, database security won't protect from a denial of service attack that, for example, issues a query doing a Cartesian product on every table in the database. However many DBMSs do have load limiting facilities for particular users that would help prevent this.

    The main message is DBMSs are sophisticated tools and considerable benefit accrues from using the in-built functionality rather than trying to build it yourself.
    jorwell
  • What I use...

    I have put in place:
    - parametrized queries
    - stored procedure
    - white listing of users' words e.g we simply refuse SQL words drop, delete, truncate, insert, update, etc ( I realise we are lucky we actually can do that)
    - the web user can only read 99pc of the tables and can update just a few through SP.

    I am lucky our website has so far resisted every attack. We have seen dozens despite being a small shop. But i am sure if hackers really wanted to they would find a way in... Our FTP was hacked twice for storing porn movies. So we have moved to white listing the FTP. Only the client ip can acces a given account. This is a constant battle and a direct cost to online businesses.

    I think more needs to be done by OS vendors, governments and ISPs to better track attackers and hackers.
    Drakkhen
  • Look, SQL injection attacks are not hard to prevent

    sanitize every single bit of input your users input!

    Lots of lazy people out there still do code like:
    String command = "Select * where id = " + textbox1.Text + " from users;"

    Also, Hackforums as an actual hacking community? I shudder at that thought, it is at best where script kiddies show off, and where you can make easy money from 12 year olds by selling them "1337 HaXor RAT!"
    the_tyrant
    • Ahh, we need an edit button

      I just realized the code up there makes absolutely no sense
      the_tyrant
      • Really?

        It is just SQL
        Duke E Love
  • TRUTH:

    .... my people say that only iOS stuff can be harmed by this!
    ZuneResurection.blogspot.com
    Ballmerfeld
  • There is no excuse

    for anyone to fall prey to a SQL injection attack these days. Well, if you are using the right languages, and know what you are doing that is.
    Zheldon
    • No Excuse

      Thanks for your comment.

      Agreed. It is surprising how many high profile sites have been taken down by this type of attack, given it is so easy to prevent.

      Gery Menegaz
      gery.menegaz
      • Xamppitis

        Can make anyone look like a web genius, yet truly dangerous in the wrong hands.
        Tired Tech
  • how to prevent it.?

    Stop using Microsoft SQL Server and that's it.
    beau parisi
    • I Don't Think So

      The "make and model" of the database back-end has little to do with SQL injection. It's the front-end that allows SQL injection to occur.
      scotton1
  • süper robot ve sünger bob oyunları

    süper robot ve sünger bob oyunları
    Organik Hayat
  • Stop authoring login systems

    The message of the recent set of hacks that website developers should get loud and clear is that authoring your own login system is hard. As in, "this is an advanced topic and you shouldn't be doing it until you know what you are doing" hard. There are prepackaged systems out there like:

    http://barebonescms.com/documentation/sso/

    That do everything your login system could ever need to do and then some. I'm using that one in some of my stuff and it is rather well written and very well documented.
    leonadams