Prevent sql injection_PHP tutorial-PHP Tutorial-php.cn

[Transfer]SQL injection
SQL Injection
Many web developers fail to realize that SQL queries can be tampered with and therefore treat SQL queries as trusted commands. As everyone knows, SQL queries can bypass access controls, thereby bypassing authentication and permission checks. What's more, it's possible to run host operating system level commands via SQL queries.
Direct SQL command injection is a technique commonly used by attackers to create or modify existing SQL statements to obtain hidden data, overwrite key values, or even execute database host operating system commands. This is accomplished by the application taking user input and combining it with static parameters into an SQL query. Some real examples will be given below.
An attacker can create a new superuser in the database due to the lack of validation of the entered data and the connection using a superuser or other database account with permission to create new users.
Example#1 A piece of code that implements paging display of data...can also be used to create a superuser (PostgreSQL system).
Copy PHP content to clipboard
PHP code:
$offset = $argv[0]; // Note, no input validation!
$query = "SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET $offset;";
$result = pg_query($conn, $query);

Generally, users will click on the "previous page" and "next page" links whose $offset has been binned. The original code only thinks that $offset is 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='postgres';
--
Then he can create a super user. Note that 0; is just to provide a correct offset to complete the original query so that it does not make errors.
Note: -- is the comment mark of SQL, which can generally be used to tell the SQL interpreter to ignore the following statements.

Taking advantage of the page showing the search results is a possible way to obtain the password. All the attacker has to do is find out which variables were submitted for SQL statements and mishandled them. Such variables are usually used in conditional statements in SELECT queries, such as WHERE, ORDER BY, LIMIT and OFFSET. If the database supports the UNION construct, an attacker may also append a complete SQL query to the original statement to obtain the password from an arbitrary data table. Therefore, it is important to encrypt the password field.
Example#2 Display article...and some passwords (any database system)
Copy PHP content to clipboard
PHP code:
$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 to the original query to get the password:
'
union select '1', concat(uname||'-'||passwd) as name, '1971-01-01', '0' from usertable;
--
If the above statement (using ' and --) is added to any variable in $query, then there will be trouble.
UPDATE in SQL is also vulnerable. This query may also be inserted or appended to another complete request as in the example above. But attackers prefer to target the SET clause so they can change some data in the table. In this case, you must know the structure of the database in order to modify the query successfully. Fields can be guessed based on variable names on the form, or brute force cracked. There are not many ways to name the fields that store usernames and passwords.
Example#3 From resetting password... to gaining more privileges (any database system)
Copy PHP content to clipboard
PHP code:
$query = "UPDATE usertable SET pwd='$pwd' WHERE uid='$uid';";

But a malicious user will submit ' or uid like '%admin%'; -- as the value of the variable to $uid to change the admin password, or submit the value of $pwd as "hehehe', admin='yes', trusted=100 "(with a space after it) to gain more permissions. By doing this, the query statement actually becomes:
Copy PHP content to clipboard
PHP code:
// $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 horrific example will demonstrate how to execute system commands on some databases.
Example#4 Attack the operating system of the host where the database is located (MSSQL Server)
Copy PHP content to clipboard
PHP code:
$query = "SELECT * FROM products WHERE id LIKE '%$prod%'";
$result = mssql_query($query);

If the attack submits a%' exec master..xp_cmdshell 'net user test testpass /ADD' -- as the value of the variable $prod, then $query will become
Copy PHP content to clipboard
PHP code:
$query = "SELECT * FROM products
WHERE id LIKE '%a%'
exec master..xp_cmdshell 'net user test testpass /ADD'--";
$result = mssql_query($query);

The MSSQL server will execute this SQL statement, including the command that follows it to add a user to the system. If this program is running as sa and the MSSQLSERVER service has sufficient permissions, the attacker 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 on other database systems. Using different methods, various databases can suffer.

Preventive Measures
Some people may comfort themselves by saying that the attacker needs to know the information about the database structure to carry out the above attack. Yes, it is. But no one can guarantee that attackers will not get this information. Once they do, the database is in danger of being leaked. If you are using an open source software package to access the database, such as a forum program, it is easy for an attacker to obtain the relevant code. The risk is even greater if the code is poorly designed.
These attacks are always based on exploiting code that is not security aware. Therefore, never trust data entered from the outside, especially from the client, including select boxes, form hidden fields, and cookies. As in the first example above, even a normal query can cause disaster.
Never use a superuser or owner account to connect to a database. Use an account with strictly restricted permissions.
Check whether the input data has the expected data format. PHP has many functions that can be used to check input, ranging from simple variable functions and character type functions (such as is_numeric(), ctype_digit()) to complex Perl-compatible regular expression functions that can do this job.
If your program is waiting for a number to be entered, consider using is_numeric() to check it, or directly use settype() to convert its type, or use sprintf() to format it as a number.
Example#5 A safer way to implement paging
Copy PHP content to clipboard
PHP code:
settype($offset, 'integer');
$query = "SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET $offset;";
// Please pay attention to %d in the format string, it is meaningless if you use %s
$query = sprintf("SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET %d;",
$offset);

Use database-specific sensitive character escape functions (such as mysql_escape_string() and sql_escape_string()) to escape non-numeric data submitted by users. If the database does not have a special sensitive character escaping function, addslashes() and str_replace() can complete this work instead. Looking at the first example, this example shows that just quoting the static part of the query is not enough and the query can be easily broken.
Avoid showing any confidence about the database at all costs, especially the database structure. See error reporting and error handling functions.
You can also choose to use features such as database stored procedures and predefined pointers to abstract database access so that users cannot directly access data tables and views. But this approach has other implications.
In addition to this, it is also a good idea to use code or a database system to save query logs where possible. Obviously, the log does not prevent any attacks, but it can be used to track which programs have been attempted attacks. The log itself is useless; you have to consult the information it contains. After all, more information is better than none.