How Do I Become an SQL Programmer?

SQL injection attacks are one of the common methods used by hackers to attack databases. With the development of B / S mode application development, more and more programmers use this mode to write applications. However, because the level and experience of programmers are also uneven, a considerable part of programmers do not judge the legitimacy of user input data when writing code, which makes the application program have potential security risks. The user can submit a piece of database query code and get some data he wants to know based on the results returned by the program. This is the so-called SQL injection, that is, SQL injection.

SQL injection attack

SQL injection is accessed from the normal WWW port, and it looks like a normal Web page access

Find the SQL injection location;

· Judgment

The SQL query code for login verification of a website is:

  strSQL = "SELECT * FROM users WHERE (name = '" + userName + "') and (pw = '" + passWord + "');" 

Malicious filling

  userName = "1 'OR' 1 '=' 1";

versus

  passWord = "1 'OR' 1 '=' 1";

Will cause the original SQL string to be filled as

  strSQL = "SELECT * FROM users WHERE (name = '1' OR '1' = '1') and (pw = '1' OR '1' = '1');"

That is, the SQL command actually run will become the following

  strSQL = "SELECT * FROM users;"

Therefore, you can log in to the website without account password. So SQL injection attacks are commonly known as hack-and-fill games.

In terms of security technology,

With the rapid development of computer technology, people are getting more and more headaches in the face of more and more "perverted" and complex threats to website technologies. They use the Internet to perform various malicious activities, such as identity theft, private information theft, and bandwidth resource occupation . After they sneak in, they will spread and constantly update themselves. These activities often use the user's curiosity to sneak into the user's PC without the user's knowledge or permission. Unconsciously, the funds in the account are transferred, and company messages are transmitted. The harm is very serious. On August 16, 2006, the first web threat sample appeared. As of October 25, 2006, the 150th variant had been produced, and it continued to evolve.

There are multiple dimensions of the target positioning of website threats. Whether it is an individual or a company or an industry, it has its own considerations. Even countries, regions, genders, races, religions, etc. have become the cause or motivation for launching attacks. Attacks can take many forms, even compound forms, such as viruses,

SQL injection attacks are a very nasty security hole. All web developers, no matter what platform, technology, or data layer, need to be sure what they understand and prevent. Unfortunately, developers often don't focus their time on this, so much so that their applications, and even worse, their customers are extremely vulnerable.

Michael Sutton has posted a very thought-provoking post about how common this problem is on the public web. He built a C # client program with Google's Search API to find sites that are vulnerable to SQL injection attacks. The steps are simple:

1. Find sites with query strings (for example, look for URLs with "id =" in the URL)

2. Send a request to those websites that are determined to be dynamic, change the id = statement in them, and add an extra single quote to try to cancel the SQL statement (for example, id = 6 ')

3. Analyze the returned response and look for words like "SQL" and "query" in it, which often means that the application returned a detailed error message (which itself is also very bad)

4. Check whether the error message indicates that the parameters sent to the SQL server have not been properly encoded. If so, it indicates that a SQL injection attack can be performed on the website.

A random sampling test of 1,000 websites found through Google search, he detected that 11.3% of them were vulnerable to SQL injection attacks. This is very, very scary. This means that hackers can remotely use the data in those applications, get any password or credit card data without hashing or encryption, and even log in to these applications as administrator. This is not only bad for developers developing websites, but also worse for consumers or users who use the website, because they provide data to the website, thinking that the website is safe.

So what exactly is a SQL injection attack?

There are several scenarios that make SQL injection attacks possible. The most common reason is that you construct the SQL statement dynamically without using the correctly encoded parameters. For example, consider the encoding of this SQL query, the purpose of which is to query the authors based on the social security number provided by the query string:

Dim SSN as String

Dim SqlQuery as String

SSN = Request.QueryString ("SSN")

SqlQuery = "SELECT au_lname, au_fname FROM authors WHERE au_id = '" + SSN + "'"

If you have SQL code like the snippet above, your entire database and application can be remotely hacked. how could be? Under normal circumstances, users will use a social security number to access this website, and the encoding is performed like this:

'URL to the page containing the above code

'SQL Query executed against the database

SELECT au_lname, au_fname FROM authors WHERE au_id = '172-32-9999'

This is what developers expect, using social security numbers to query author information in the database. But because the parameter values are not coded correctly, a hacker can easily modify the value of the query string and embed additional SQL statements after the value to be executed. for example,

'URL to the page containing the above code

'SQL Query executed against the database

SELECT au_lname, au_fname FROM authors WHERE au_id = ''; DROP DATABASE pubs-

Note that there is no, you can add "'; DROP DATABASE pubs-" to the end of the SSN query string value, and terminate the current SQL statement with the ";" character. The other parts are commented out with "-" strings. Because the SQL statement is manually constructed in the encoding, and finally the string is passed to the database, the database will first query the authors table, and then delete our pubs database. With a bang, the database is gone!

In case you think the result of an anonymous hacker deleting your database is bad, unfortunately, this is actually better in the situations involved in SQL injection attacks. A hacker can not simply destroy the data, but use the weakness of the above coding to execute a JOIN statement to get all the data in your database and display it on the page, allowing them to obtain user names, passwords, credit card numbers, etc. They can also add UPDATE / INSERT statements to change the price of the product, add a new administrator account, and really screw up your life. Imagine when you check your inventory by the end of the month and find that the actual number of products in your warehouse is different from the number reported by your accounting system.

SQL injection attacks are something you need to worry about, no matter what web programming technology you use, and all web frameworks need to worry about this. You need to follow a few very basic rules:

1) When constructing dynamic SQL statements, be sure to use a type-safe parameter encoding mechanism. Most data APIs, including ADO and ADO. NET, have such support, allowing you to specify the exact type of the provided parameters (eg, string, integer, date, etc.), which can ensure that these parameters are escaped / encoded properly To prevent hackers from using them. Be sure to use these features from beginning to end.

For example, in ADO.NET for dynamic SQL, you can rewrite the above statement as follows to make it safe:

Dim SSN as String = Request.QueryString ("SSN")

Dim cmd As new SqlCommand ("SELECT au_lname, au_fname FROM authors WHERE au_id = @au_id")

Dim param = new SqlParameter ("au_id", SqlDbType.VarChar)

param.Value = SSN

cmd.Parameters.Add (param)

This will prevent someone from trying to secretly inject another SQL expression (because ADO.NET knows to encode the string value of au_id), and avoid other data problems (such as incorrect conversion of numeric types, etc.). Note that the TableAdapter / DataSet designer built into VS 2005 uses this mechanism automatically, as does the ASP.NET 2.0 data source control.

A common misperception is that if you use stored procedures or ORMs, you are completely immune to SQL injection attacks. This is incorrect, you still need to make sure that you are careful when passing data to the stored procedure, or that you are safe when using ORM to customize a query.

2) Always perform a security review before deploying your application. Establish a formal security process that reviews all coding every time you make an update. The latter point is particularly important. Many times I heard that the development team will do a detailed security review before going live, and then after a few weeks or months they make some small updates, they will skip the security review. Guan said, "It's just a small update, and we'll do a coding review later." Please always do a security review.

3) Never store sensitive data in the database in clear text. My personal opinion is that passwords should always be stored after one-way hashing, and I don't even like storing them after encryption. By default, the ASP.NET 2.0 Membership API does this for you automatically, and also implements a secure SALT randomization behavior. If you decide to build your own member database, I suggest you check out the source code of our own Membership provider we published here. Also make sure that credit cards and other private data in your database are encrypted. This way, even if your database is compromised, at least your customers' private data will not be used.

4) Make sure you write automated unit tests to specifically verify that your data access layer and applications are protected from SQL injection attacks. This is very important to help catch the oversight of the "just a small update, all without security issues" situation, to provide an extra layer of security to avoid accidentally introducing bad Security flaws go into your application.

5) Lock down the security of your database and give only the minimum permissions required to access the database's web application functions. If the web application does not need to access some tables, then verify that it does not have permission to access those tables. If the web application only needs read-only permissions to generate reports from your account payables table, then make sure that you disable its insert / update / delete permissions on this table.

6) Many novices download the SQL universal anti-injection system program from the Internet, which is used to prevent other people from performing manual injection testing in the header of the page that needs to prevent injection

However, if you use the SQL injection analyzer, you can easily skip the injection prevention system and automatically analyze its injection point. Then in just a few minutes, your administrator account and password will be analyzed.

7) For the prevention of injection analyzer, the author found a simple and effective prevention method through experiments. First we need to know how the SQL injection analyzer works. During the operation, it was found that the software was not directed to the "admin" administrator account, but to the authority (such as flag = 1). In this way, no matter how your administrator account changes, you cannot escape detection.

Step 3: Since we cannot escape detection, we will create two accounts, one is an ordinary administrator account, and the other is an account that prevents injection. Why do you say that? The author thinks that if an account with the highest authority is used to create an illusion and attract the detection of software, and the content of this account is more than a thousand words of Chinese characters, it will force the software to enter the full load state and even resources when analyzing this account Run out and crash. Let's modify the database.

Modify the table structure. Modify the data type of the administrator's account field, and change the text type to the maximum field of 255 (actually enough, if you want to make it bigger, you can choose the remark type), and the password field is also set the same.

Modify the table. Set the account with administrator privileges in ID1, and enter a large number of Chinese characters (preferably greater than 100 characters).

Put the real administrator password in any position after ID2 (such as on ID549).

Because SQL injection attacks are targeted at improper programming during application development, this attack is "legitimate" for most firewalls. The solution of the problem only depends on perfect programming. There are fewer tools specifically targeted at SQL injection attacks. Wpoison is helpful for development with asp and php ...