Abstract SQL (parameterized) Query

What is parameterized query?

I. Definition

Parameterized Query (Parameterized Query or Parameterized Statement) refers to a Parameter (Parameter) that is used to give a value where a value or data needs to be filled in when connecting to the database and accessing data, this method is currently regarded as the most effective way to defend against SQL Injection attacks.

Some developers may think that using parameterized query will make the program less difficult to maintain, or it will be inconvenient to implement some functions. However, the extra development cost caused by using parameterized query, it is usually far lower than the major loss caused by the SQL Injection Attack Vulnerability being discovered.

Principle

When parametric query is used, the database server does not regard the parameter content as a part of the SQL command for processing. Instead, parameters are applied to run only after the database completes the compilation of the SQL command, therefore, even if the parameter contains an invalid command, it will not be run by the database.

SQL instruction Writing Method

When writing SQL commands, parameters are used to represent the values to be filled in, for example:

Microsoft SQL Server

The parameter format of Microsoft SQL Server is "@" followed by the parameter name. SQL Server also supports anonymous Parameters "? ".

SELECT * FROM myTable WHERE myID = @ myID

Insert into myTable (c1, c2, c3, c4) VALUES (@ c1, @ c2, @ c3, @ c4)

Microsoft Access

Microsoft Access does not support named parameters, but only supports anonymous Parameters "? ".

UPDATE myTable SET c1 = ?, C2 = ?, C3 =? WHERE c4 =?

MySQL

The MySQL parameter format is "? "Character with the parameter name.

UPDATE myTable SET c1 =? C1, c2 =? C2, c3 =? C3 WHERE c4 =? C4

Oracle

The Oracle parameter format is composed of the ":" character and the parameter name.

UPDATE myTable SET c1 =: c1, c2 =: c2, c3 =: c3 WHERE c4 =: c4

PostgreSQL

The parameter format of PostgreSQL is composed of the "$" character and the parameter sequence number.

UPDATE myTable SET c1 = $1, c2 = $2, c3 = $3 WHERE c4 = $4

PostgreSQL also supports Parameter Representation of Oracle

--------------------------------------------------------------------------------

Summarize the definitions of parameter symbols in each database:

SQLSERVER @

Access, MySQL?

Oracle:

PostgreSQL $

The above symbols are supported by native databases, but specific to ADO. NET calls, using the native. in addition to Oracle, various databases can use the @ symbol to represent parameters in SQL statements;

What are the requirements for using OleDB or ODBC drivers for each database? Symbol represents a parameter.

There are other databases not mentioned in this Article. Their SQL statements indicate that the parameter symbols may be different. How can we process them in a program? The topic of this article begins:

2. Abstract SQL parametric Query

In the PDF. NET data development framework, the parameter definition is always processed using #. The specific format is as follows:

# Parameter name [: parameter type], [data type], [parameter length], [parameter output input type] #

In the above definition, the content in brackets is optional.

For details, see SQL-MAP specifications"

The example in the first part of this article can be rewritten as follows:

 
 

  
  
UPDATE myTable SET   
  
  
c1 = #c1#,   
  
  
c2 = #c2:String#,   
  
  
c3 = #c3:String,Sring,50#   
  
  
WHERE c4 = #c4:Int32#  
 
 

If the parameter type is not specified, the default value is String, for example, c1.

When the program is running, it will replace the # internal parameters with the appropriate parameter content according to the current specific database access program instance.

The above parameter form is written in the SQL-MAP configuration file, such as the following an actual SQL-MAP query script:

 
 

  
  
<Select CommandName = "GetStatisticsAnalysis_SalerRoleStatistics" CommandType = "Text" Method = "" Description = "" ResultClass = "DataSet">
  
  
<! [CDATA [
  
  
SELECT a. Role, a. Sales amount/10000 sales amount, a. proportion FROM [GetStatisticsAnalysis_SalerRoleStatistics] (
  
  
# Manageid Int32: ##,# min: String #,# max: String #) a]>
  
  
</Select>
 
 

In this way, the parameters of different types of database queries are completely shielded, and SQL parameterized queries are abstracted.

This article seems to be over, but the title "parameterization" in this article adds a bracket, indicating that we abstract not only parameters, but also the entire SQL query.

3. Abstract SQL query: SQL-MAP Technology

In the second part of this article, we abstract the parameters in SQL. We can further abstract the entire SQL and look at the following abstract process:

This idea is SQL-MAP, which maps SQL statements into programs.

The following describes how the PDF. NET data development framework operates stored procedures. Of course, this is true for a single SQL statement. Of course, the operation of a single SQL statement, we do not have to ask for the SQL-MAP of this "Heavyweight" approach, or use the framework of The ORM technology OQL, but this is not the topic discussed in this article.

First, write the following script in the SQL-MAP configuration file:

 
 

  
  
<Select CommandName = "GetProductManage_FundSaleAndAIP" Method = "" CommandType = "Text" Description = "retrieve XXX list" ResultClass = "DataSet">
  
  
<! [CDATA [
  
  
Select * from GetProductManage_FundSaleAndAIP (# Type: String #, # Name: String #, # isAIP: String #)
  
  
]>
  
  
</Select>
 
 

Note that the ResultClass attribute in the script maps the query to a single value, DataSet, object class, and object class set.

With this SQL-MAP file, we can use code tools to automatically generate the following code (you can also hand-write ):

 
 

  
  
/// <Summary>
  
  
/// Obtain the XXXXX list
  
  
/// </Summary>
  
  
/// <Param name = "Type"> </param>
  
  
/// <Param name = "Name"> </param>
  
  
/// <Param name = "isAIP"> </param>
  
  
/// <Returns> </returns>
  
  
Public DataSet GetProductManage_FundSaleAndAIP (String Type, String Name, String isAIP)
  
  
{
  
  
// Obtain Command Information
  
  
CommandInfo into info = Mapper. GetCommandInfo ("GetProductManage_FundSaleAndAIP ");
  
  
// Assign values to parameters. This method is recommended;
  
  
Using info. DataParameters [0]. Value = Type;
  
  
Using info. DataParameters [1]. Value = Name;
  
  
Using info. DataParameters [2]. Value = isAIP;
  
  
// Assign values to parameters. Use the naming method;
  
  
// Raise info. SetParameterValue ("@ Type", Type );
  
  
// Raise info. SetParameterValue ("@ Name", Name );
  
  
// Raise info. SetParameterValue ("@ isAIP", isAIP );
  
  
// Execute the query
  
  
Return CurrentDataBase. ExecuteDataSet (CurrentDataBase. ConnectionString, using info. CommandType, using info. CommandText, using info. DataParameters );
  
  
//
  
  
} // End Function
 
 

From the process above, we can see that the framework uses the SQL-MAP technology, the SQL statement (including a variety of queries of a single SQL statement and stored procedures, etc.) mapped into the DAL Layer Code, the entire process does not need to understand. NET development technology, so the DAL layer code can be fully written by DBA, and business developers only need to call the DAL code.

Using this technology, DBAs can write efficient SQL statements with database features. To change the database, you only need to change the configuration file without rewriting the program.

Digress:

The idea of SQL-MAP is not PDF. NET data development framework exclusive, in fact, this idea is also from the famous iBatis framework, but unlike iBatis, PDF. NET SQL-MAP parameters do not need to define a special "parameter class", do not need to write additional XML files to indicate how the query results map with the entity class, so the entire development process is greatly simplified, to simplify it, you only need to write SQL statements to write the DAL code.