SQL-injection: how to use them and how to defend against them

sql-injection-logoSQL (Structured Query Language) is a powerful language for working with relational databases, but quite a few people are in fact ignorant of the dark side of this language, which is called SQL-injection. Anyone who knows this language well enough can extract the needed data from your site by means of SQL – unless developers build defenses against SQL-injection, of course. Let’s discuss how to hack data and how to secure your web resource from these kinds of data leaks!

How to use SQL injection

SQL injection is in fact a technique that can extract secret data from a database or destroy it. Hackers enter a malicious code into SQL statements via web page input and get all the data in which they are interested.

The SQL injection topic is too intricate to address the theoretical part, so the effectiveness of SQL injection will be shown in examples. We will create a one page test news site, where news will be pulled up. First, we create a news table:

And a site page with a simple design (that page html, incl. db migration, you might get here in case you wanna try it yourself). The news items are rendered thru requests with GET parameter id:sql-injection-001-short

Find a chink

Now we will test SQL-injections (SQLI). The first step – find the chink in website (incl. db) functionality. In an address bar given http://sqlinjection/view.php?id=1, if we change the address to http://sqlinjection/view.php?id=1″ (note a double quote at the end of the url) the site will show an error:sql-injection-002-short

Ok, we have found the error. It’s caused by the incorrect site php code:

How to protect a website from this kind of injection, we will discuss a bit later. Now we will see how hackers take advantage of this kind of loose php script to SQL query sintax.

Quick start. Apply injection into the SQL query

For example, we have a page that executes only one record. To extract all records we should use the following malicious query:


In fact, if that url input parameter is not sanitized, we get a db query this way:

Because of 1=1 is always true, we get all data from the table. So, we have found the first exception (injection vulnerability)!sql-injection-003-short

Get ready for attack!

We know that the website has flaws, so in the next stage we need to use an SQL operator called UNION. By way of the result of the UNION SELECT construction, we can inquire how many columns a table has.

Let’s try to get the quantity of columns by the trial-and-error method.


Returns error…sql-injection-004-short




SQL query in this case is:

sql-injection-005-shortYes! No errors, there are five columns in the news table. This information about the table can help us to extract data from other tables or to change it. To make it easier, try using GROUP BY instead of UNION SELECT


Here SQL-query is:

No errors, the count of columns is more than four.


Oops, error… then the column count is less than 8.

So, we enquire using a range which is between 4 and 8. Then we try “GROUP BY 5” and “GROUP BY 7” and there is an answer – 6 columns. GROUP BY is easier, but we use UNION as an example because this is a popular operator for SQLI. Also, ORDER BY can be used.

Let us suppose that we know that in the DB there is a users table. To extract data from the users table we could use the following:



Without errors! That’s how we get a record from a user table. We set the id value “-1” to dispose of unnecessary records from the news table, so if you don’t want to get a record from db – all you need is to set a nonexistent id, for example, -1.

Are there any other records in the table users?



No, there is only one record in that table. Now, instead of first and second column we set “username” and “password” (as is usually possible):



Here, injecting a SQL code through the web url we got data from an another table! As you can see, the UNION is a very powerful instrument in SQL injection.

Building a defense against SQL-injection

People used to think that it’s easier to attack than to protect. In the SQL injection world nowadays, it’s easier to protect than to attack. All you need is to filter input data:

This approach is called type casting. Now the id parameter can be only of an integer type. When we try to put in the extra characters “  ‘ ” the php sanitizing script filters it out:


As the result: no errors!

And when we retry entering the request by extracting username and password we will see:


Prepared statement in SQL

Prepared statement is a convenient feature to execute SQL queries with high efficiency. It’s a SQL query template which is using as filter-layer to connect to the database. Each character is prepared to be stored into db table. If I want to store car”); delete from users;  (with double quote) the statement will not mix the last double quote with queries’ quote by escaping special characters, e.g.

This prevents harmful injections like this:  car”); delete from users; 


Always use PDO (php data object) in your PHP-projects. PDO provides security in working with a DB by using correctly prepared statements:

If we don’t use prepared statements, our db tables would be at risk of exposure or deletion.


Also, you can use frameworks (Laravel, Yii2, Symphony, etc.). They have an additional ORM layer with correctly prepared SQL queries.

Happy Ending

Nowadays, SQL injections are not as popular as 15 years ago. All you need is to take advantage of this basic advice to protect your web resource. We strongly recommend using SQL injections for fun, and don’t forget to protect yourself from them.

Web parsing php tools

Almost all developers have faced a parsing data task. Needs can be different –  from a product catalog to parsing stock pricing. Parsing is a very popular direction in back-end development; there are specialists creating quality parsers and scrapers. Besides, this theme is very interesting and appeals to the tastes of everyone who enjoys web. Today we review php tools used in parsing web content. more…

PHPxcel for importing and exporting data in working with Excel

Sometimes when you are developing a project, it might be necessary to do a parsing of xls documents. To give an example: you do a synchronization between xls worksheets and a website database, and you need to convert xls data to the Mysql and want to do it completely automatically.

If you work with Windows it is simple enough – you just need to use COM objects. However, it is another thing if you work with PHP and need to make it work under the UNIX systems. Fortunately there are many classes and libraries for this purpose. One of them is the class PHPExcel. This library is completely cross-platform, so you will not have problems with portability.  more…

Web Scraping with Java and HtmlUnit

java-htmlunit-post-front-cover-smallWeb scraping or crawling is the act of fetching data from a third party website by downloading and parsing the HTML code to extract the data you want. It can be done manually, but generally this term refers to the automated process of downloading the HTML content of a page, parsing/extracting the data, and saving it into a database for further analysis or use. more…

The present trends in web scraping tools

Recently I got a question from one of the blog readers. After I replied to it, I decided to share it with a wider audience.


I found your scraping.pro site and found it very helpful, then realized the web scraper solutions rating was from 2014.  What is the best solution for today?   I have lots of sites I need to scrape, mainly search then drill-down sites.   I would like to be able to schedule the scraping to run on a daily basis.  Is there a direction you could point me?  I’m a seasoned developer by trade but am seeing all these point and click solutions (e.g. import.io) and am wondering if I should stick with Node.JS or .NET or if I should investigate some of these GUI scrapers of today.


Back to top