SQL parameters and SQL Injection in Java

 

Today, most programs use SQL statements to deal with databases more or less. In program code, SQL statements exist as strings, such as "select column1, column2 ,... From Table1 where param_column1 = value1 and param_column2 = 'value2 '". In this example, the values of param_column1 and param_column2 in the Data columns are value1 and value2 as the filter conditions, and column1, column2, and other data columns are queried from table 1. Here, param_column1 is an integer type and param_column2 is a string type. In many cases, the values of value1 and value2 can be determined only when the program is running. This means that SQL statements can be dynamically generated only when the program is running. Many developers prefer to use String concatenation to generate SQL statements, such as "select column1, column2 ,... From Table1 where param_column1 = "+ int_to_str (int_value) +" and param_column2 = '"+ str_value +"' ". C programmers may prefer to format strings in a format similar to "select column1, column2 ,... From Table1 where param_column1 = % d and param_column2 = '% s' ", the SQL statement is sent to the database engine after % is replaced by the actual value. From the perspective of SQL Execution, string formatting is not much different from String concatenation. Different SQL statements are submitted to the database each time the query conditions change slightly. Although the differences between these SQL statements may be small, the query engine has to re-Parse each time, resulting in a reduction in execution efficiency. In addition, if param_column1 or param_column2 is binary data in the preceding example, it is obviously troublesome to generate an SQL statement. More importantly, this method of generating SQL statements has security risks, which can easily lead to SQL injection attacks. The preceding SQL statement is used as an example. If the value specified by param_column2 is "'or'' =' (double quotation marks are not included), the obtained SQL statement is "select column1, column2 ,... From Table1 where param_column1 = value1 and param_column2 = ''or'' = ''", so that the WHERE clause is virtually empty and all records in Table1 will be queried. If the attacker finds that the value specified for param_column2 is "'; delete from Table1 where'' =' "(without double quotation marks), the obtained SQL statement is" select column1, column2 ,... From Table1 where param_column1 = 0 and param_column2 = ''; delete from Table1 where'' = ''". After execution, Table1 is cleared.