Anti-SQL injection

[]sql injection
SQL injection
Many Web developers do not notice that SQL queries can be tampered with, thus treating SQL queries as trustworthy commands. SQL queries can bypass access control, bypassing authentication and permission checking. What's more, it is possible to run host operating system-level commands through SQL queries.
Direct SQL Command injection is a technique commonly used by attackers to create or modify existing SQL statements to achieve hidden data, or to overwrite critical values, or even to execute database host operating system commands. This is accomplished by using the application to obtain user input and combining static parameters into SQL queries. Some real examples will be given below.
The attacker can create a new superuser in the database because of the lack of validation of the data entered and the use of a superuser or other database account that has permission to create a new user.
example#1 A section of code that implements data paging ... It can also be used to create a super User (PostgreSQL system).
Copy PHP content to clipboard
PHP code:
$offset = $argv [0];//note, no input validation!
$query   = "SELECT ID, name from:" $offset by name LIMIT OFFSET; ";
$result = pg_query ($conn, $query);

A typical user clicks on a link to the previous page, next page of the bin value $offset. The original code would only think that $offset is a numeric value. However, if someone tries to process the following statement first by UrlEncode () and then adding the URL:
0;
INSERT into Pg_shadow (USENAME,USESYSID,USESUPER,USECATUPD, passwd)
    select ' Crack ', usesysid, ' t ', ' t ', ' crack '
    from Pg_shadow where Usename= ' Postgres ';
--
so that he can create a super user. Pay attention to that 0; Just to provide a correct offset to complement the original query so that it doesn't go wrong.
Note:--is an annotation tag for SQL, which can generally be used to tell the SQL interpreter to ignore subsequent statements.

The start of a page that displays search results is a viable way to get a password. The only thing an attacker would have to do was to find out which of the submitted variables were used for SQL statements and mishandled them. Variables of this type are usually used for conditional statements in a SELECT query, such as where, order by, LIMIT, and OFFSET. If the database supports the UNION construct, an attacker might also attach a complete SQL query to the original statement to get the password from any data table. Therefore, it is important to encrypt the password field.
Example#2 Display Articles ... and some passwords (any database system)
Copy PHP content to clipboard
PHP code:
$query   = "SELECT ID, name, inserted, size from products
 & nbsp;                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 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 the $query, then it is troublesome.
UPDATE in SQL can also be attacked. This query may also be inserted or appended to another complete request as the example above. But attackers are more likely to start with SET clauses, so they can change some data in the datasheet. In this case, you must know the structure of the database in order to modify the query successfully. You can guess the field by the variable name on the form or brute force. There are not many named methods for fields that hold user names and passwords.
Example#3 from Reset Password ... To get more permissions (any database system)
Copy PHP content to clipboard
PHP Code:
$query = "UPDATE usertable SET pwd= ' $pwd ' WHERE uid= ' $uid ';";

But a malicious user would put ' or uid like '%admin% '; --The value of the variable is submitted to the $uid to change the password of the admin, or the $PWD value is submitted as "Hehehe ', admin= ' yes ', trusted=100" (followed by a space) to obtain more permissions. By doing so, 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 gruesome example will show how to execute system commands on some databases.
Example#4 attacks the operating system of the host on which the database resides (MSSQL Server)
Copy PHP content to clipboard
PHP Code:
$query = "SELECT * from the products WHERE id like '% $prod% '";
$result = Mssql_query ($query);

If the attack is submitted a% ' exec master. xp_cmdshell ' net user Test Testpass/add '-the value $prod as a variable, 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 executes this SQL statement, including the command that is used to add users to the system. If the program is run with SA and the MSSQLServer service has sufficient privileges, the attacker can obtain a system account to access the host.
Note: Although the above examples are for a particular database system, this does not mean that you cannot perform similar attacks on other database systems. Using different methods, all kinds of databases are likely to suffer.

Precautions
Perhaps some would comfort themselves by saying that an attacker would have to know the structure of the database to implement the attack above. Yes, indeed. But no one can guarantee that the attackers will not be able to get the information, but they get the risk that the database will be compromised. If you are using an Open-source package to access a database, such as a forum program, the attacker would be very tolerant of the relevant code. If the code is poorly designed, the risk is even greater.
These attacks are always based on discovering code that is not strong in security sense. Therefore, never trust data entered by the outside world, especially from clients, including selection boxes, form hiding fields, and cookies. As the first example above, even a normal query can cause disaster.
Never use a superuser or owner account to connect to a database. The account to be strictly restricted with permissions. The
checks whether the data entered has the desired data format. PHP has a number of functions that can be used to check input, from simple variable functions and character type functions (such as is_numeric (), Ctype_digit ()) to complex Perl-compatible regular expression functions to do this work.
If the program waits to enter a number, consider using Is_numeric () to check it, or use Settype () to convert its type, or use sprintf () to format it as a number.
Example#5 A more secure way to make pagination
copy PHP content to the Clipboard
PHP code:
Settype ($offset, ' Integer ');
$query = "SELECT ID, name FR OM Products ORDER BY name LIMIT OFFSET $offset; ";
//Note the%d in the format string, which is meaningless if you use%s
$query = sprintf ("Select ID, name from [] ORDER by name LIMIT OFFSET%d;" ,
                 $offset);

uses database-specific, escape functions such as mysql_escape_string () and sql_escape_string () to escape the Non-numeric data submitted by the user. Addslashes () and Str_replace () can replace the completion of this work if the database does not have a dedicated, sensitive character escape function. Take a look at the first example, which shows that it is not enough to enclose the static part of the query, and that the query is easily compromised.
do whatever it takes to avoid showing any confidence in the database, especially the database structure. See Error reporting and error handling functions.
You can also choose to abstract library Access using attributes such as stored procedures and predefined pointers for databases so that users do not have direct access to data tables and views. But this approach has other implications.
In addition, it is also a good idea to use code or database systems to save query logs, if allowed. Obviously, the log does not prevent any attack, but it can be used to track which program has been tried to attack before. The log itself is useless, and you need to review the information contained therein. After all, more information is better than nothing.