SQL Injection problems that may be caused by batch in queries

SQL Injection problems that may be caused by batch in queries

When we use in or for query at some time, in order to speed up, we may often use the concatenation between SQL statements and then directly import them into an in statement, in fact, the performance of such queries is still acceptable,

For example:

update keyword set stats=? where taskid in ('"+CollUtil.toString(list, "','")+"') "

Of course, this in contains some column data (), but if the data contains some SQL-sensitive keywords or symbols, SQL injection will occur, for example, if a single quotation mark (Baidu) appears in an in query, which is a sensitive character in SQL, this will cause your statement to fail to be executed. If we do not pay attention to these issues when writing code, it will cause some hacker attacks. For example, if a hacker makes a statement that contains the annotation symbol recognized by MySQL, then the hacker performs a delete operation directly after splicing, so your database system is finished.

In fact, we 'd better not eliminate these problems by ourselves, because unexpected situations may occur, so when we perform operations such as query or change insert, it is best to use a question mark expression to prevent injection.

However, in some special circumstances, if our system enables internal use, we can also use in or query as appropriate, But we () pay attention to the quantity problem in the brackets. This problem is caused by the differences in the quantity estimation in different versions. We 'd better use some algorithms to control the amount of data. We 'd better do a stress test on our own to see how much data your in contains. Of course, I have done a stress test, in can contain more than characters. At that time, I contained 64 characters of English letters and Chinese characters. During the operation, the field length may be different and the speed will certainly be different.

Int size = ids. size (); int loopNum = (size % 1000 = 0 )? (Size/1000) :( size/1000 + 1); if (size = 0) {return;} for (int cp = 1; cp <= loopNum; cp ++) {int beginRecord = (cp-1) * 1000; int endRecord = cp * 1000; if (endRecord >=size) {endRecord = size ;} // obtain the List of tasks in batches <String> list = ids. subList (beginRecord, endRecord );

Of course, I am just an example of a grouping algorithm. We usually use this kind of query performance that is not very good. We also need to pay attention to grouping. If this is not the case, mySQL may report some packet Too large exceptions or check your version exceptions. If you find that your SQL statement is correct, you should pay attention to this problem.

Another point is that when we write an SQL statement in a function, we try not to use the second SQL statement because it is very time-consuming to open and close the database, therefore, when writing a program using a programming language, we should try to use some classes provided to us in our tool class, such:

  StringBuffer buffer = new StringBuffer();              buffer.append("update keyword set stats=? ");              paramsList.add(stats);              if(stats==Stats.pend.getCode()){                  buffer.append(",pend=? ");                  paramsList.add(new Date());              }              buffer.append("  where taskid in ('"+CollectionUtil.toString(list, "','")+"') ");

In this way, if two SQL statements are met, one SQL statement can be used directly. This also increases the speed of code execution. In particular, when the data volume is very large, we need to reduce the SQL statements in a function, and try to splice them to reduce the opening and closing of the database.

Implement fuzzy query and prevent SQL injection. First Aid

// Define variables
String txt_editor = ultraTextEditor4.Text;
StringBuilder str_where = new StringBuilder ();
Str_where.Append ("AND ");
Str_where.Append (SqlFactory. CreateWhereExpression (CtbClass1.IC _ ORG_NAME, WhereExpression. LIKE, "'%" + @ txt_editor + "%'"). BuildSql ());

SQL Injection Problems

Common asp injection prevention program to eliminate SQL Injection risks and improve website security

Generally, http requests are similar to get and post requests, so we only need to filter out invalid characters in the parameter information of all post or get requests in the file, therefore, we can filter http request information to determine whether it is being attacked by SQL injection.
IIS passed to asp. dll get requests are in the form of strings, when passed to the Request. after QueryString data, the asp parser analyzes the Request. queryString information, and then separate the data in each array according to "&", so the get interception is as follows:
First, we define that the request cannot contain the following characters:
'| And | exec | insert | select | delete | update | count | * | % | chr | mid | master | truncate | char | declare | xp_mongoshell
Each character is separated by "|", and then the obtained Request. QueryString is determined.
The Code is as follows:

Program code:

Dim SQL _injdata
SQL _injdata = "'| and | exec | insert | select | delete | update | count | * | % | chr | mid | master | truncate | char | declare | xp_cmdshell"
SQL _inj = split (SQL _Injdata, "| ")

If Request. QueryString <> "Then
For Each SQL _Get In Request. QueryString
For SQL _Data = 0 To Ubound (SQL _inj)
If InStr (Request. QueryString (SQL _Get), SQL _Inj (SQL _DATA)> 0 Then
Response. Write ("<script language =" "javascript" "type =" "text/javascript" "> alert (" "the submitted information contains invalid characters! ""); </Script> ")
Response. End ()
End If
Next
Next
End If

In this way, we implement the get request injection interception, but we also need to filter the post request, so we have to continue to consider the request. form, which also exists in the form of an array. We only need to make another round-robin judgment. The Code is as follows:

Program code:
If Request. Form <> "" Then
For Each SQL _Get In Request. Form
For SQL _Data = 0 To Ubound (SQL _inj)
If InStr (Request. QueryString (SQL _Get), SQL _Inj (SQL _DATA)> 0 Then
Response. Write (&... the remaining full text>