Is there a real injection vulnerability to universal paging stored procedures?

Original: Does the universal paging stored procedure really have an injection vulnerability?

Read two articles about stored procedure SQL injection vulnerabilities today:

1): This is an efficient and common paging stored procedure with SQL injection vulnerability

2): Anti-SQL injection: Generate parameterized Generic paging query statements

How to see how uncomfortable, in my mind stored procedure is not an injection of vulnerability ah? At least my current level does not know how to inject stored procedures. If you have an injection method please advise. In other words, the stored procedure itself is not an injection vulnerability, but most of the vulnerabilities are due to procedural vulnerabilities.

Let's simplify the next two-bit discussion of the paging stored procedure, the original code is too long, I'm here to write a single table query for a stored procedure. Create a user table with the following table structure: There are three fields, a person ID, and a name field.

CREATE TABLE [dbo]. [Person] (
[ID] [int] NULL,
[Last_Name] [varchar] (COLLATE) Chinese_prc_ci_as NULL,
[First_Name] [varchar] (COLLATE) Chinese_prc_ci_as NULL

) on [PRIMARY]

Then write a query stored procedure (Getperson): function, according to different conditions to read the user information.

IF (EXISTS (SELECT *
From sysobjects
WHERE id = object_id (N ' [dbo].[ Getperson] ')
and OBJECTPROPERTY (ID, N ' isprocedure ') = 1))
BEGIN
DROP PROCEDURE [dbo]. [Getperson]
END
Go
CREATE PROC Getperson
@strWhere VARCHAR (100) = "--Query criteria (note: Do not add where)
As
BEGIN
DECLARE @strSQL VARCHAR (1000)--the subject sentence
SET @strSQL = ' Select Top ten * from the person where 1=1 '
-If there is a condition, add
IF @strWhere! = "
BEGIN
SET @strSQL = @strSQL + @strWhere
END
PRINT (@strSQL)
EXEC (@strSQL
)
END

Query method, according to the user's last name to query. To make the final stored procedure execute syntactically correct, without an injection vulnerability, the correct format for the condition is: and first_name like '%jim ' s dog% '.

We can see that when Jim's dog is assembled into SQL, the middle of the single quotation mark must become two. In order to avoid injection, I generally deal with the security of SQL splicing: in C # Writing programs should be written like this:

<summary>
Masking special characters in a string
by Minjiang 07-07-06
</summary>
public string Saferequest (String str)
{
Defines the string to return
String Sreturn;
Convert the string you want to work to a lowercase letter
str = str. ToLower ();
Defining Special strings
String Sql_kill = "' |and|exec|insert|select|delete|update|count|*|%

|chr|mid|master|truncate|char|declare|set|;| From|=|--|drop|<|> ";
char[] Separator ={' | '};
string[] sql = Sql_kill. Split (separator);
for (int i=0;i<sql. Length; i++)
{
If there is a special character then replace it with an empty
if (str. INDEXOF (SQL [i]. ToString (). ToLower ()) >-1)
{
Replace single quotation marks with double quotes
if (Sql[i]. ToString () = = "'")
{str = str. Replace ("'", "" ");}
Else
{
Replace sensitive characters with empty
str = str. Replace (Sql[i]. ToString (). ToLower (), "");
}

}

}
Sreturn = str;
return sreturn;


}

if (susername!= "")
{
Strwhere + = "and first_name like '%" +this. Saferequest (susername) + "%" "

}

opportunity for paged stored procedure injection: The above universal paging stored procedure would say that there is an opportunity for SQL injection because of the single quotation mark after the wildcard like, and if the single quotation marks in the subsequent arguments match the single quotation marks that follow the similar wildcard character, What follows is the content of SQL injection. At this point, we can write a method of filtering SQL special characters, processing the special characters, may choose the appropriate filter according to their own conditions. At the very least, replace the single quotation mark in the user name with double quotation marks. The following wording is unsafe: There are single quotes in the user name, for example: Jim's dog

if (susername!= "")
{
Strwhere + = "and first_name like '%" +susername+ "% '"

}

Note: Park Friends Small no mentioned, can be by the input injection conditions encoded into a hex code to deceive the filter program. This does exist, and all can be compared in hexadecimal for content that is in the HTML tag that is also a SQL-sensitive character. For example: ',;. "-" no processing, because it will not be HTML encoded.

I personally do not support this so-called efficient universal paging stored procedure for the following reasons:

1: The readability is too poor, the whole version of the string, who looked uncomfortable.

2: There is a high security requirement for the application, and a little attention will be paid to the above mentioned injection vulnerability.

3: Inability to find complex queries on multiple tables. If forced to apply, I would like to write a stored procedure far more troublesome than writing alone.

4: The so-called universal, that is, most people know you the approximate structure of the stored procedure, so that undoubtedly give the ulterior motives more to take advantage of the machine.

For the process of paging stored procedures, take a look at this: how do you face a large number of paging needs?

Summary: The universal paging stored procedure itself is not a loophole, but the process of the lack of rigor caused by injection opportunities.

Solution to the potential pitfalls of this splicing of SQL strings:

1: Try to set the input parameters type, can be set to digital type must be set to digital type.

2: Set the length of the parameter, a string, such as name, typically no more than 20 characters.

3: Input parameter content can delete the space is best to use trim (), so that, even if there are SQL sensitive characters, once the SQL connection into a string, it is not able to inject normal.

4: Try to filter the incoming conditions, at least replace the single quotation marks with double quotes.

5: Strictly set the database user's permissions, responsible for querying the user, only let it have read permissions, so that is injected success, can not cause fatal consequences.

Users with Insert permissions, strict control of Delete, updated permissions. And the helper has the deletion permission of the user, the general helper has the view permission, the deletion operation is difficult to exist SQL injection.

Note:

You are welcome to express your views!

Is there a real injection vulnerability to universal paging stored procedures?