The basic method of SQL on special character processing

Be sure to pay enough attention to special characters such as quotes, angle brackets, and so on that the user may enter, which can cause serious security problems. One of the basic techniques of SQL injection is to exploit security vulnerabilities that are not filtered by single quotes.

The user's input is no more than two uses: to the database operation or display on the page, the following two cases of special characters in the processing of the description.

1. Operations on the database
User input data for the operation of the database, but also divided into two cases, one is to write the library operation, the second is as a query condition.

1.1 Write Library operations
(INSERT and update are treated as write-Library operations, and this results in insert as an example, update is handled the same)
Generally use INSERT statement or AddNew method to write the library operation, we first look at the INSERT statement:

DIM Username,sqlstr
Username = Trim (Request.Form ("uname"))
Sqlstr = "INSERT INTO [UserInfo] (username) VALUES (' & username & ')"

In SQL Server, for example, write a library in this way, and if the username contains single quotes ('), an error occurs. You can convert single quotes by using the following custom function:

Rem Convert SQL Illegal character
function Sqlencode (fstring)
If IsNull (fstring) Then
Sqlencode = ""

Exit function
End If
Sqlencode=replace (fstring, "'", "" ")
End Function

The above function converts a single quotation mark to two consecutive single quotes, which the database accepts and writes in single quotes. The SQL statement is changed to:

Sqlstr = "INSERT INTO [UserInfo] (username) VALUES ('" & Sqlencode (username) & "]"

Then look at the AddNew method:

DIM username
Username = Trim (Request.Form ("uname"))
' Myrst is a Recordset object, MyConn is a connection object
Myrst.open "[UserInfo]", myconn,0,3
Myrst.addnew
Myrst ("username"). Value = Username
Myrst.update
Myrst.close

When you write a library this way, you do not have to call Sqlencode () to convert the single quote, and the database handles itself.

For parameters of a stored procedure, you also do not have to convert single quotes.

We recommend that you use stored procedures to operate, the benefits, I have in the "ASP and stored Procedures" in the article has been elaborated. Otherwise, it is recommended to use the AddNew method to write the library, the advantage is not only to avoid the single quotation mark processing, this article does not make in-depth discussion.

1.2 User input as query criteria
Web Teaching Network

If the data entered by the user appears as a query condition in the WHERE clause, the single quotation marks are converted regardless of whether the WHERE clause belongs to an UPDATE statement, a DELETE statement, or a SELECT statement.

2. Data entered by the user as output, displayed on the page
We only discuss situations where the user is not allowed to use HTML code, that is, even if the user enters HTML code, the data is not displayed as HTML code. As for the user to allow the use of HTML code, more complex, later in the article discussion.

The data entered by the user is absolutely not to be processed and displayed as is. If it contains HTML or JS code, it is trivial to make your pages cluttered, and you can even turn off your hard drive.

The output is displayed on the page, either directly by the user or from the database. You can see that the above processing in the warehousing only converted single quotes, for angle brackets, double quotes and other special characters have not been processed, we put in the output time to process.

The Server.HTMLEncode () method in ASP converts many characters to HTML characters, such as converting < to, > to >, and so on.

You can use the server before the data is displayed on the page. HTMLEncode () to convert it. However, the method does not convert to carriage returns and spaces, which creates the following problem: If the user is entering data through the textarea control, the output will not retain the original format, not only without carriage return line, multiple spaces will only appear as one. To solve this problem, we use the following custom function:

Rem converts HTML illegal characters, for output display when
function HTMLEncode (fstring)
If not IsNull (fstring) Then
fstring = Replace (fstring, ">", ">")
fstring = Replace (fstring, "<", "<")
fstring = Replace (fstring, CHR (34), "") ' double quote
fstring = Replace (fstring, CHR (39), "'") ' single quote
fstring = Replace (fstring, CHR () &CHR (32), "") ' Space
fstring = Replace (fstring, CHR (9), "") ' tab value
fstring = Replace (fstring, CHR (), "<br>") ' Line wrap
fstring = Replace (fstring, CHR (13), "") ' Enter
HTMLEncode = fstring
End If
End Function

Call the above function, output the data entered through the textarea control, you will get a satisfactory result.

You can use the server if the data output is in a form control, regardless of the control. The HTMLEncode () method converts characters, even for textarea controls, and does not cause problems. Although a carriage return space is not converted, it can be recognized in the control. However, the server. The HTMLEncode () method does not convert single quotes. Therefore, the value of the control must use double quotes: webjx.com

<input type=text name=uname value= "" "& Server. HTMLEncode (username) & "" >

Otherwise, if the user enters a ' onclick=javascript: ..., the above code will appear as:

<input type=text name=uname value= ' onclick=javascript:...>

and JavaScript commands can do a lot of things.

Above, through the user input data two kinds of uses, to the special character processing has made the approximate explanation. In another case, the user's input is the parameter value of the GET request. For example, send a request to the server via the following URL: test.asp?username=myname

I usually only do this type of data submission and validate the data type when I receive it. If you have character data, how do you handle special characters? Interested friends think about it, hehe.

Some friends like to filter special characters with JavaScript and limit the number of characters they enter. I do not recommend this, one is that JavaScript is run by the client, unreliable. You know, the request to the server can be forged, the forger will not add your JavaScript code, and second, JavaScript is not very friendly, three, in fact, there is no need to limit so many characters, too many restrictions, users will be afraid.

Summarize my experience with special characters:

1. Verify the data types received;
2. Try to manipulate the database through stored procedures;
3. If not, try to write the library using the AddNew method,
4. Use a custom Function Sqlencode () for data that is a query condition. Convert single quotes;
5. The value of the form control must be enclosed in double quotes;
6. When displaying data in a form control, use the server. HTMLEncode () method converts characters;
7. For data submitted through textarea, use the Custom function HTMLEncode () to convert the characters and keep the formatting;
8. Avoid using request () to receive data, use Request.Form () or Request.QueryString ();
9. Try to avoid passing character arguments by URL (passing numeric arguments in this way only)