new player January 27, 2011 at 11:37 am

Fighting SQL injection with using PHP

Closet *

SQL injection is the most dangerous type of attack, because it is behind countless cases of hacking of surviving corporate websites and portals, and just personal home pages. In fact, it is quite easy to protect your project - to do this, you first need to understand what the essence of this problem is and make some changes to the code to protect it.

What is SQL injection

SQL injection is the action you take to execute your own database query without your knowledge. Most often this happens when you invite the user to send some information to the server, but instead of the desired information, the attacker sends his request, which, through negligence, is fulfilled by the server. Using such a request, an attacker can not only obtain prohibited information from the database, but also, under certain conditions, make changes to it, as well as execute system commands.

Examples of SQL injections

An attacker can send his request not only by entering it in the information input field (using $_POST), he can also substitute his $_GET variables in the address bar, or manually change his $_COOKIE. Therefore, caution should be exercised when working with these global data sets.

If you have a form on your page for entering certain information, an attacker can use its fields for their own purposes. Instead of the expected strings (name, password, etc.), he will enter his own request into such a field.

Most of these SQL injections look like this:

Asd" or 1=1--

Let's say we have a form for user authorization. If we enter code like this in the login field, we can use our SQL injection to gain access even without proper checks. How does this work? Let's consider what kind of request we will receive as a result of our actions:

SELECT * FROM users WHERE username = "asd" or 1=1--" and password = "asd"

So, as you can see from the example, our code will be executed successfully. And since the expression 1=1 will always return true, we are guaranteed to gain access.

You might be wondering what the double hyphen (--) is for. This double dash at the end of the line tells the SQL server to ignore the rest of the queries. If you want to write an injection not for a SQL server, then in this case, you will need to replace the double hyphen with a single apostrophe.

Please note that the above example is simply the most standard option, but far from the only one. Their number is simply amazing, and it all depends on how the attacker’s head works.

Examples of some more common injections:

") or ("1"="1
"or "1"="1
" or "1"="1
Or 1=1--
"or 1=1--
"or 1=1--

It is also quite common for attackers to use the address bar (URL) for their attacks. This method, like the previous one, is no less dangerous. When the server uses PHP and MySQL (the most popular combination at the moment), the address to the script usually looks something like this:

Http://somesite.com/login_script.php?id=1

By adding a little SQL to such a line you can do terrible things:

Http://somesite.com/login_script.php?id=1‘; DROP TABLE login; #

In this case the sign is used # instead of a double hyphen, since it tells the SQL server to ignore all subsequent queries that come after ours. And the most dangerous thing (if you haven't noticed yet) is that we just told the server to take away the sign with the users. This example clearly demonstrates how dangerous SQL injections can be.

What you need to do to protect your scripts from SQL injections

We already know that SQL injection vulnerabilities occur when information from users enters a database query without appropriate processing. So the next step is to write secure scripts.

Fortunately, this danger has been known for quite some time. PHP even has a special function (since version 4.3.0) that combats this type of attack - mysql_real_escape_string.

mysql_real_escape_string makes a string safe for use in database queries by escaping all potentially dangerous characters. Typically, this sequential character is a single apostrophe ("), which will be escaped (\") after using this function.

In order to protect against SQL injection, all external parameters ($_GET, $_POST, $_COOKIE) should be included in SQL query, work with mysql_real_escape_string(), and in the request itself place them in a single apostrophe. If you adhere to this simple rule, then the attacker's actions will lead to the formation of safe queries, since all the text of his SQL injections is now inside apostrophes.

Let's look at what the server actually does during this processing:

SQL injection (the string that the attacker entered in the “Login” field instead of his login):

Sql" or 1=1--

$name = mysql_real_escape_string($_POST["username"]);
$res = mysql_query("SELECT * FROM users WHERE username = "".$name."" and password = "asd"");

SELECT * FROM users WHERE username = "sql\" or 1=1--" and password = "asd"

That is, with such a request, instead of dangerous actions, we try to select the data of a user who has a rather strange username (sql\"or 1=1-).

You can go further and write a functionality that will automatically process the $_GET, $_POST and $_COOKIE arrays accordingly, but it all depends on your wishes. The only thing you need to remember is that you need to protect all places where data from the user is transferred to the database.

While it is still clear that an attacker must have at least some knowledge of the structure of the database to carry out a successful attack, obtaining this information is often very simple. For example, if the database is part of an open-source or other publicly available software package with a default installation, this information is completely open and accessible. This data can also be obtained from a closed project, even if it is coded, complicated, or compiled, and even from your personal code through the display of error messages. Other techniques include using common (easy to guess) table and column names. For example, a login form that uses the "users" table with column names "id", "username" and "password".

Most successful attacks are based on code written without appropriate security requirements. Don't trust any input, especially if it comes from the client, even lists on a form, hidden fields, or cookies. The first example given shows how such requests can lead to disaster.

Never connect to a database using account database owner or superuser. Always try to use specially created users with the most limited rights.
use prepared expressions with bound variables. This feature is provided by extensions PDO , MySQLi and other libraries.
Always check the data you enter is of the expected type. PHP has many functions for data validation: starting from the simplest functions for working with variables And character type detection functions(such as is_numeric() And ctype_digit() respectively) and ending Perl-compatible regular expressions.

In case the application expects digital input, use the function ctype_digit() to check the entered data, or force specify its type using settype() , or simply use the numeric representation using the function sprintf() .

Example #5 Safer implementation of pagination navigation

settype ($offset, "integer");
$query = "SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET$offset ;" ;

// pay attention to the %d format, using %s would be pointless
$query = sprintf ( "SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET %d;",
$offset);

If bound variables are not supported at the database level, then always escape any non-numeric data used in database queries using special escape functions specific to the database you are using (for example, mysql_real_escape_string() , sqlite_escape_string() etc.). Common features such as addslashes() They are only useful in certain cases (eg MySQL in single-byte encoding with NO_BACKSLASH_ESCAPES disabled), so it is best to avoid using them.
Under no circumstances display any information about the database, especially about its structure. Please also read the relevant sections of the documentation: " Error messages" And " Error processing and logging functions ".
You can use stored procedures and predefined cursors to work with data in an abstract way without giving users direct access to data and views, but this solution has its own challenges.

In addition to all of the above, you can log queries in your script or at the database level, if it supports it. Obviously, logging cannot prevent damage from happening, but it can help in tracing a compromised application. The log file is useful not in itself, but in the information it contains. Moreover, in most cases it is useful to log all possible details.

One of the most significant issues for web programmers is the security of PHP scripts. All programmers use it to one degree or another. various methods to secure your project, but, unfortunately, in most cases, protection against several vulnerabilities is used, while other problem areas are not even considered.
This article lists the main types of PHP vulnerabilities and discusses ways to protect against them.

Types of PHP vulnerabilities

Showing errors to the user
Meaning: if there are any errors in the code, information about the error that occurred is displayed to the user. This is not a critical vulnerability, but it could cause an attacker to get additional information about the structure and operation of the server.
Availability of data on system characteristics to the user
Meaning: The user can access data that provides insight into the system. It is not a critical vulnerability, but it could allow an attacker to obtain additional information about the structure and operation of the server. The reason for this vulnerability is the programmer’s errors and “oversights.” An example is the presence of a file phpinfo.php with a function of the same name in the public domain.
Availability of program code data to the user
Meaning: the user can access the program codes of modules that have an extension other than php. It is a critical vulnerability, as it allows an attacker to obtain sufficiently complete information about the structure and operation of the server and identify its vulnerabilities.
Simple passwords to access administrative pages
Meaning: an attacker can guess a simple password for the administrative page, giving him more opportunities for hacking. This is a critical vulnerability, as it allows an attacker to affect the operation of the server.
Ability to set global variables
Meaning: when incorrect PHP settings It is possible to set global script variables via the query line. It is a critical vulnerability, since an attacker can influence the progress of the script in his favor.
PHP injection
Meaning: into a parameter that determines PHP work with files or program code, a link to a third-party program code or the code itself. It is a critical vulnerability, since an attacker can execute his scripts on the server. Code execution is carried out using functions: eval(), preg_replace(), require_once(), include_once(), include(), require(), create_function(), readfile(), dir(), fopen().
PHP injection via file upload
Meaning: if it is possible to set global variables, a link to third-party program code or a confidential file on the server is passed into the parameter that defines the file to be uploaded to the server. This is a critical vulnerability because an attacker can execute scripts on the server or gain access to confidential data. This vulnerability is possible only if it is possible to set global variables and the incorrect organization of the file loading mechanism.
email injection
Meaning: a parameter that determines how PHP works with by email, a link to third-party program code or the code itself is transmitted. This is a critical vulnerability, since an attacker can execute his scripts on the server or gain access to data stored by the user.
SQL injection
Meaning: the parameter defining the SQL query contains data that forms a request for accessing private data. This is a critical vulnerability because an attacker can obtain confidential data stored in the database. To change a query, an attacker can use the following constructs: SELECT, UNION, UPDATE, INSERT, OR, AND.
Cross-site scripting or XSS
Meaning: third-party program code is passed to the parameter that determines the data displayed to the user. This is a critical vulnerability because an attacker can obtain confidential data stored in the client's browser. The attacker uses HTML tags to change the request.

Rules for writing secure code in PHP

Blocking error output
To do this, just set error_reporting(0) in the program code or add the line php_flag error_reporting 0 in the .htaccess file
Usage complex passwords to access administrative pages
To do this, it is enough to use multi-valued passwords that do not have semantic meaning (for example, K7O0iV98dq).
Logging critical user actions
Does not provide protection directly, but allows you to identify hackers and determine the vulnerabilities they exploited. To do this, the user’s actions and the data transmitted by him, which relate to critical moments of the system’s operation, are sufficient to be recorded in a regular text file.
An example of the logging function and its operation:
function writelog($typelog, $log_text) (
$log = fopen("logs/".$typelog.".txt","a+");
fwrite($log, "$log_text\r\n");
fclose($log);
}
writelog("authorization", date("y.m.d H:m:s")."\t".$_SERVER["REMOTE_ADDR"]."\tSuccessful login");
Closing access to site modules
Provides protection against attempts to view or execute their contents. To do this, just configure access to module files in the .htaccess file using the FilesMatch and Files constructs.
For example, we block access to all modules with the php extension, except for the capcha.php file:
Disabling the ability to set global variables
To do this, just set register_globals = off; in the server settings. or in the .htaccess file add the line php_flag register_globals off . Using ini_set("register_globals",0); The problem will not be solved by setting the variables before the script starts running.
Disabling the ability to use deleted files
To do this, just set allow_url_fopen = off in the server settings. . This provides partial protection against PHP injections, but not complete, since an attacker can transmit not a link to a file with program code, but the program code itself. To fully protect against PHP injections, you must additionally use filtering of incoming data. Sometimes this protection measure cannot be used due to the nature of the project (you need to access remote files).
Filtering incoming data
Provides protection against most vulnerabilities. There is no universal solution. It is advisable to use a check against a “white” list of characters in conjunction with a check for prohibited words. “White” is the list of allowed characters. This list should not include dangerous characters, such as . Prohibited words include: script, http, SELECT, UNION, UPDATE, exe, exec, INSERT, tmp, as well as html tags.
Example of filtering incoming data:
// Check against the white list. Only Russian and Latin letters, numbers and signs are allowed _-
if (preg_match("/[^(\w)|(A-Yaa-ya-)|(\s)]/",$text)) (
$text = "";
}
// Filtering dangerous words
if (preg_match("/script|http|||SELECT|UNION|UPDATE|exe|exec|INSERT|tmp/i",$text)) (
$text = "";
}
Checking for file upload using HTTP POST
Provides protection against PHP injections through file uploads. To ensure this, files uploaded to the server must be checked with the is_uploaded_file() function or moved with the move_uploaded_file() function. This type of protection can not be used if the ability to set global variables is disabled.
Escaping quote characters in data passed to the database
Provides protection against SQL injections. The most optimal method is to process all incoming non-numeric data using the mysql_real_escape_string() function. You can also use automatic screening of incoming data. To do this, just add the line php_value magic_quotes_gpc on to the .htaccess file, but this method is not reliable, as it can lead to double escaping.
An example of escaping quotes using the mysql_real_escape_string() function:
if (!is_numeric($text)) (
$textrequest = mysql_real_escape_string($text);
}
Converting special characters to html entities before output
Provides protection against XSS. To do this, the data entered by the user, which may contain unwanted html tags, is simply processed in the output using the htmlspecialchars() function. This type of protection can not be used if filtering of incoming data eliminates dangerous HTML tags.

As you can see, creating a well-thought-out script security system is not as time-consuming as it seems.
This article is not intended to be a textbook on script security, but I hope that it will encourage PHP programmers to use more thoughtful security methods.

We wish you success in completing it. The results of your passage will be published later (follow the news on social networks), and all those who have passed will also be sent a invite to register on the site.

Like, share with friends and colleagues, repost on social networks.

All programmers have read or at least heard about methods for hacking website security. Or even encountered this problem. On the other hand, the imagination of those who want to break the site is endless, so all bottlenecks must be well protected. That's why I'd like to start a series of short articles that will introduce basic website hacking methods and techniques.

In the first article, I would like to describe and explain some common methods for hacking one of the most vulnerable parts of the site - forms. I'll go into detail about how to use these techniques and how to prevent attacks, as well as cover security testing.

SQL injection

SQl injection is a technique where an attacker enters SQL commands into an input field on a web page. This imput can be anything - a text field in a form, _GET and _POST parameters, cookies, etc. This method was very effective before the advent of frameworks in the PHP world. But this hack can still be dangerous if you don't use an ORM or any other extensions to the data object. Why? Due to the way parameters are passed to the SQL query.

"Blind" injections

Let's start with a classic example of an SQL statement that returns the user by his login and password hash (login page)

Example 1

mysql_query("SELECT id, login FROM users WHERE login = ? and password = hash(?)");

I put question marks in the expression because of the different variations of this solution. The first option, in my opinion, is the most vulnerable:

Example 1a

Mysql_query("SELECT id, login FROM users WHERE login = "" . $login . "" and password = hash("" . $password . "")");

In this case, the code does not check for invalid data input. Values are passed directly from the input form to the SQL query. In the best case scenario, the user will enter his username and password here. What's the worst case scenario? Let's try to hack this form. This can be done by passing "prepared" data. Let's try to log in as the first user from the database, and in most cases this is the admin account. To do this, we will pass a special string instead of entering the login:

" OR 1=1; --

The first quote can also be a single quote, so one attempt at hacking may not be enough. At the end there is a semicolon and two hyphens so that everything that comes after turns into a comment. As a result, the following SQL query will be executed:

SELECT id, login FROM users WHERE login = “;” OR 1=1 LIMIT 0.1; - and password = hash(“;Some password”)

It will return the first user from the database and possibly log in to the application as that user. A good move would be to add LIMIT to log in as each individual user. This is the only thing needed to go through each value.

More serious ways

In the previous example, everything is not so scary. The options in the admin control panel are always limited and it would take a lot of work to actually break the site. But an attack through SQL injection can lead to much greater damage to the system. Think about how many applications are created with the main table "users" and what would happen if an attacker entered code like this into an unprotected form:

My favorite login"; DROP TABLE users; --

The "users" table will be deleted. This is one of the reasons to make database backups more often.

_GET parameters

All parameters filled out through the form are transmitted to the server using one of two methods - GET or POST. The most common parameter passed via GET is id. This is one of the most vulnerable places for attacks, and it does not matter what type of URL you use - ` http://example.com/ users/?id=1`, or ` http://example.com/ users/1`, or ` http://......./.../ post/35 `.

What happens if we insert the following code into the URL?

Http://example.com/users/?id=1 AND 1=0 UNION SELECT 1,concat(login,password), 3,4,5,6 FROM users WHERE id =1; --

Probably, such a request will return the user's login and... a hash of his password. The first part of the request `AND 1=0` turns what precedes it into false, so no records will be received. And the second part of the request will return data in the form of prepared data. And since the first parameter is id, the next one will be the user’s login and the hash of his password and some other parameters. There are many programs that use brute force to decode a password like the one in the example. And since the user can use the same password for different services, it is possible to gain access to them.

And here’s what’s curious: it’s completely impossible to defend against this type of attack using methods like `mysql_real_escape_string`, `addslashes`, etc. d. Basically, there is no way to avoid such an attack, so if the parameters are passed like this:

"SELECT id, login, email, param1 FROM users WHERE id = " . addslashes($_GET["id"]);"

the problems will not go away.

Escaping characters in a string

When I was new to programming, I had a hard time working with encodings. I didn't understand what the difference was between them, why use UTF-8 when you need UTF-16, why the database always sets the encoding to latin1. When I finally started to understand all this, I discovered that there would be fewer problems if I kept everything in one coding standard. While sorting through all this, I also noticed security problems that arise when converting from one encoding to another.

The problems described in most of the previous examples can be avoided by using single quotes in queries. If you use addslashes() , SQL injection attacks that rely on single quotes escaped with a backslash will fail. But such an attack can work if you simply substitute a character with code 0xbf27 , addslashes() converts it into a character with code 0xbf5c27 - and this is a completely valid character single quote. In other words, `뼧` will go through addslashes() and then MySQL mapping will convert it into two characters 0xbf (¿) and 0x27 (‘).

"SELECT * FROM users WHERE login = ""; . addslashes($_GET["login"]) . ";"";

This example can be hacked by passing 뼧 or 1=1; -- in the login field in the form. The SQL engine will generate the final query like this:

SELECT * FROM users WHERE login = "¿" OR 1=1; --

And it will return the first user from the database.

Protection

How to protect the application? There are a lot of methods, the use of which will not make the application completely invulnerable, but will at least increase its security.

Using mysql_real_escape_string

The addslashes() function is unreliable because it does not allow for many hacking cases. mysql_real_escape_string does not have such problems

Using MySQLi

This MySQL extension can work with related parameters:

$stmt = $db->prepare("update uets set parameter = ? where id = ?"); $stmt->bind_param("si", $name, $id); $stmt->execute();

Using PDO

Long way to substitute parameters:

$dbh = new PDO("mysql:dbname=testdb;host=127.0.0.1", $user, $password); $stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)"); $stmt->bindParam(":name", $name); $stmt->bindParam(":value", $value); // insert one row $name = "one"; $value = 1; $stmt->execute();

Short way:

$dbh = new PDO("mysql:dbname=testdb;host=127.0.0.1", $user, $password); $stmt = $dbh->prepare("UPDATE people SET name = :new_name WHERE id = :id"); $stmt->execute(array("new_name" => $name, "id" => $id));

Using ORM

Use ORM and PDO and bind (use bind) parameters. Avoid SQL in your code, if you see SQL in your code then there is something wrong with it.

ORM will take care of security in the bottlenecks in the code and parameter validation.

Conclusions

The purpose of this series is not to provide complete guide to hack websites, but to ensure the security of the application and prevent attacks from any source. I tried to write this article not only for programmers - they should be aware of any threats in the code and know how to prevent them, but also for quality engineers - because their job is to track and report such issues .

How to hack a form? Sql injection. Fighting SQL injection with PHP Php injection for beginners