SQL Injection for PHP database security

Source: Murong Xiaoyu Blog

Thanks to the sharing of superhei, the original address is http://cn.php.net/manual/zh/security.database.sql-injection.php

Many web developers have not noticed that SQL queries can be tampered with, so they regard SQL queries as trustworthy commands. However, SQL queries can bypass access control and bypass authentication and permission checks. Furthermore, it is possible to run the Host OS-level commands through SQL queries.
Direct SQL command injection is a common technique used by attackers to create or modify existing SQL statements, so as to obtain hidden data or overwrite key values, even the purpose of executing database host operating system commands. This is achieved through applications that obtain user input and combine static parameters into SQL queries. The following are some examples.
Attackers can create a new super user in the database because they lack the authentication of input data and use superusers or other database accounts with the permission to create new users to connect. Example 27-2. A piece of code to display data by page ...... It can also be used to create a super user (PostgreSQL system ).
<? Php

$ Offset = $ argv [0]; // note that no verification is entered!
$ Query = "SELECT id, name FROM products order by name LIMIT 20 OFFSET $ offset ;";
$ Result = pg_query ($ conn, $ query );

?>

Generally, users will click the "Previous Page" and "next page" links of the $ offset value. The original code only considers $ offset as a numerical value. However, if someone tries to urlencode () the following statement first and then add it to the URL:

0;
Insert into pg_shadow (usename, usesysid, usesuper, usecatupd, passwd)
Select crack, usesysid, t, t, crack
From pg_shadow where usename = S;
--

Then he can create a Super User. Note that the value 0 is only used to provide a correct offset to supplement the original query, so that no error occurs.

Note: -- is the annotation mark of SQL, which can be used to tell the SQL interpreter to ignore subsequent statements.
It is a feasible way to get a password for the page that displays the search result. What Attackers need to do is to find out which submitted variables are used for SQL statements and handle them improperly. These variables are usually used in SELECT query condition statements, such as WHERE, order by, LIMIT, and OFFSET. If the database supports UNION construction, attackers may also attach a complete SQL query to the original statement to obtain the password from any data table. Therefore, encryption of password fields is very important. Example 27-3. Show the article ...... And some passwords (any database system)
<? Php

$ Query = "SELECT id, name, inserted, size FROM products
WHERE size = $ size
Order by $ order LIMIT $ limit, $ offset ;";
$ Result = odbc_exec ($ conn, $ query );

?>

You can add another SELECT query based on the original query to obtain the password:

Union select 1, concat (uname |-| passwd) as name, 1971-01-01, 0 from usertable;
--

If the preceding statement (Use and --) is added to any variable in $ query, it will be troublesome.

SQL UPDATE is also under attack. Such queries may also be inserted or appended to another complete request as in the preceding example. However, attackers prefer the SET clause so that they can change some data in the data table. In this case, you must know the database structure before the query can be modified successfully. You can use the variable name in the form to guess the field or perform brute-force cracking. There are not many naming methods for fields that store user names and passwords. Example 27-4. Reset the password ...... To get more permissions (any database system)
<? Php
$ Query = "UPDATE usertable SET pwd = $ pwd WHERE uid = $ uid ;";
?>

However, malicious users submit the or uid like % admin %; -- as the variable value to $ uid to change the admin password, or submit the $ pwd value as "hehehe, admin = yes, trusted = 100 "(followed by a space) to get more permissions. In this case, the query statement is actually changed:

<? Php

// $ Uid = or uid like % admin % ;--
$ Query = "UPDATE usertable SET pwd =... WHERE uid = or uid like % admin % ;--";

// $ Pwd = "hehehe, admin = yes, trusted = 100"
$ Query = "UPDATE usertable SET pwd = hehehe, admin = yes, trusted = 100 WHERE
...;";

?>

The following terrible example shows how to execute system commands on some databases. Example 27-5. Attack the operating system of the host where the database is located (MSSQL Server)
<? Php

$ Query = "SELECT * FROM products WHERE id LIKE % $ prod % ";
$ Result = mssql_query ($ query );

?>

If the attacker submits a % exec master .. xp_mongoshell net user test testpass/ADD -- as the value of the variable $ prod, $ query will become

<? Php

$ Query = "SELECT * FROM products
WHERE id LIKE % a %
Exec master.. xp_cmdshell net user test testpass/ADD --";
$ Result = mssql_query ($ query );

?>

The MSSQL Server executes this SQL statement, including the command used to add users to the system. If the program runs with sa and the MSSQLSERVER service has sufficient permissions, attackers can obtain a system account to access the host.

Note: although the above example is for a specific database system, it does not mean that similar attacks cannot be carried out against other database systems. Different methods may cause various databases to suffer.
Preventive actions
Some may comfort themselves by saying that the attacker must know the database structure information before carrying out the above attacks. That's right. However, no one can ensure that attackers will not be able to obtain the information. Once they get the information, the database may be leaked. If you are using open-source software packages to access the database, such as forum programs, attackers can easily get the relevant code. If these codes are poorly designed, the risk is even greater.
These attacks are always based on code that lacks security awareness. Therefore, never trust external input data, especially from clients, including selection boxes, form hidden fields, and cookies. As in the first example above, even normal queries may cause disasters.
Never use a superuser or owner account to connect to a database. Use accounts with strictly limited permissions.
Check whether the input data has the expected data format. PHP has many functions that can be used to check input, from simple variable functions and character type functions (such as is_numeric (), ctype_digit ()) this can be done by using complex Perl-Compatible Regular Expression functions.
If the program is waiting to enter a number, you can use is_numeric () to check it, or directly use settype () to convert its type, or use sprintf () to format it as a number. Example 27-6. A method for better paging Security
<? Php

Settype ($ offset, integer );
$ Query = "SELECT id, name FROM products order by name LIMIT 20 OFFSET $ offset ;";

// Note the % d in the format string. If % s is used, it will be meaningless.
$ Query = sprintf ("SELECT id, name FROM products order by name LIMIT 20 OFFSET % d ;",
$ Offset );

?>

Use the database-Specific Sensitive Character escape functions (such as mysql_escape_string () and SQL _escape_string () to escape non-numeric data submitted by the user. If the database does not have a special sensitive character escape function, addslashes () and str_replace () can be used in place to complete this task. Look at the first example. This example shows that it is not enough to enclose the static part of the query with quotation marks, and the query is easily broken.
Do not show any confidence in the database, especially the database structure. See error reports and error handling functions.
You can also use the database stored procedures, predefined pointers, and other features to abstract the database access, so that users cannot directly access data tables and views. However, this method has other influences.
In addition, it is also a good way to save query logs by using code or database systems when permitted. Obviously, logs cannot prevent any attacks, but they can be used to track which program has been attacked. The log itself is useless. You must check the information contained in the log. After all, more information is better than nothing.

Add a note User Contributed Notes
SQL Injection
17-Mar-2006
If you use the PEAR package and prepare ()/execute () your queries,
You will hardly have to worry about any of this. Of course, its still
A good idea to make sure youre putting valid data in your database...
Bee at askbee dot net
17-Nov-2005 :52
A great article of how to avoid SQL injection
Html> http://www.askbee.net/articles/php/ SQL _Injection/ SQL _injection.html
Anonymous
26-Jan-2005
Here is a useful class that deals with SQL injection:

Http://www.phpinsider.com/php/code/SafeSQL/