High-performance Secure SQL stitching

Many developers in the SQL splicing headache, do not know how to splicing operation will be more secure without compromising performance, below I take the stored procedure as an example to share with you a relatively safe and efficient method

Summary : Stored Procedures (Stored Procedure) are in a large database system , a set of SQL statements in order to complete a specific function , stored in the database, after the first compilation after the re-call does not need to compile again, The user executes it by specifying the name of the stored procedure and giving the parameter (if the stored procedure has parameters). Stored procedures are an important object in a database, and any well-designed database application should use stored procedures.

format: CREATE PROCEDURE [owner.] stored procedure name [Parameter # #,... Parameter #1024)][with{recompile | Encryption | RECOMPILE, encryption}][for replication]as program line where the stored procedure name cannot exceed 128 characters. A maximum of 1024 parameters per stored procedure (SQL Server 7.0 or later), parameters are used as follows: @ parameter name data type [VARYING] [= default value] [OUTPUT]Case：requirements: 1, query a picture table of the first 10 pictures informationCondition: 1, add person number by picture type (Imgtype) (Agentid)2. If the picture type parameter is empty (NULL) query all types3. If the add person parameter is empty (NULL) query all personnelLet's look at some of the shortcomings of the two different ways we often use them . One : (not recommended)　Advantages: First, reading or writing simple to meet the process -oriented thinking, most programmers likeInsufficient: One, cannot prevent SQL injection problem; ( anti-SQL injection core method: Parameterization )second, impact performance (this argument cannot be determined cause each call will be recompiled ) third, the impact of special symbols can not be avoidednotation two : (not recommended) 　　Advantages: 1, less code, easy to read2. Prevents SQL injection problemsInsufficient: 1, hidden a time bomb (very horrible thing)1.1, when the data and parameters are NULL when the query conditions become null=null,null=null really set up? now everyone should understand the reason, this method is easy to lead to data loss (the current query data display Null=null set up or not with the SQL Server configuration, for this time the wrong way best not, otherwise you will regret to find a problem) 2, Big data This writing performance is very low (missing the index key)2.1, the use of stored procedure reason (1, improve performance, 2, security, 3, logic business ...) Improve query performance under Big Data (except for hardware, architecture: , indexing is important but differentresults in an index-building loss analysis:　　　　　　Instead, this is a higher performance.
three : (recommended) Advantages: First, reading or writing simple to meet the process-oriented thinking, most programmers likesecond, parameterization to solve the problem of SQL injection三、一次 compiled by no need to compile againiv. use of indexing function (improve performance)v. Preventing the effects of special symbols

High-performance Secure SQL stitching