SQL Injection Vulnerability Explanation

Vulnerability impact

An attacker who exploited the vulnerability could cause 1. Web pages have been tampered with 2. Data tampering 3. Core data is stolen 4. The server where the database resides is hacked into a puppet host

Workaround:

1. Filter the content entered by the user and check if there is any illegal content in the content entered by the user. such as, | (vertical bar symbol), & (& Symbol),; (semicolon), $ (dollar sign),% (percent sign), @ (at sign), \ ' (single quotation mark), "(quotation mark), \\\ ' (backslash escaped single quotation mark), \ \" (Backslash escape quotation mark), <> (angle brackets), () (brackets), + (plus sign), CR (carriage return, ASCII 0x0d), LF (line break, ASCII 0x0a),, (comma), \ \ (backslash),) (closing parenthesis), and so on.

2. Filter dangerous SQL statement keywords, such as select,from,update,insert,delete.

3. Use stored procedures to abstract data access so that users do not directly access tables or views. When using stored procedures, use ADO command objects to enforce them to harden the variable type.

4. Use the anti-injection system.

5. Repair Example

asp

Here are two possible ways to protect your WEB application from SQL injection attacks:

5.1 Use stored procedures without a dynamically constructed SQL query string. The way that parameters are passed to SQL Server stored procedures prevents the use of single quotation marks and hyphens.

The following is a simple example of how to use stored Procedures in asp:

\ ' Visual Basic example

Dim DS as DataSet

Dim myconnection Assqlconnection

Dim mycommand Assqldataadapter

Dim SelectCommand as string= "SELECT * from users where username = @username"

...

MYCOMMAND.SELECTCOMMAND.PARAMETERS.ADD (Newsqlparameter ("@username", SqlDbType.NVarChar, 20))

MyCommand.SelectCommand.Parameters ("@username"). Value =usernamefield.value

C # example

String selectcmd = "SELECT * from Authors where state = @username";

SqlConnection myconnection =new SqlConnection ("server= ...");

SqlDataAdapter mycommand =new SqlDataAdapter (Selectcmd, MyConnection);

MYCOMMAND.SELECTCOMMAND.PARAMETERS.ADD (Newsqlparameter ("@username", SqlDbType.NVarChar, 20));

mycommand.selectcommand.parameters["@username"]. Value= Usernamefield.value;

5.2 You can use validation controls to add input validation to the Web forms page. Validation controls provide an easy-to-use mechanism for standard validation of all common types, such as testing whether validation dates are valid, verifying that values are in scope, and customizing how to write Validation. In addition, validation controls enable you to fully define how error messages are displayed to the user. Validation controls can be used with any control that is processed in the class file of the Web forms page, including HTML and Web server controls. To ensure that user input contains only valid values, you can use one of the following validation controls:

5.2.1 "RangeValidator": Checks whether the user entry (value) is between the specified upper and lower bounds. You can check the range within the paired numbers, alphabetic characters, and dates.

5.2.2 "RegularExpressionValidator": Checks if an entry matches the pattern defined by the regular expression. This type of validation enables you to examine a sequence of characters in a predictable sequence of characters, such as an e-mail address, phone number, postal code, and so on. Finally: validation controls do not prevent users from entering or changing the page processing process; They only set the error state and generate an error message. The programmer's job is to test the state of the control in the code before performing further application-specific operations. There are two ways to check the validity of user input: 1. To test the general Error state: In your code, test the IsValid property of the page. This property summarizes the IsValid property values for all validation controls on the page (using logical AND). If one of the validation controls is set to invalid, the page property returns False. 2. Test the error state of individual controls: loop through the Validator collection in the page, which contains references to all validation controls. You can then examine the IsValid property of each validation control.

Php

5.3 Filtering user input

Before you pass any data to SQL queries, you should always use filtering techniques to properly filter. This cannot be overemphasized in any way. Filtering user input allows many injection defects to be corrected before they reach the database.

5.4 Quoting user input

All user data is enclosed in single quotation marks, regardless of the data type, as long as the database allows it.

5.5 Escaping data values

If you use MySQL4.3.0 or newer versions, you should use Mysql_real_escape_string () to escape all strings. If you are using an older version of MySQL, you should use the mysql_escape_string () function. If you are not using MySQL, you can choose to use specific transcoding features for specific databases. If you do not know the swap function, you can choose to use a more general swap function, for example, Addslashes ().

If you use the PEAR DB Database abstraction layer, you can use the Db::quote () method or use the? such as the query placeholder, which automatically escapes the value of the replacement placeholder. Reference http://ca3.php.net/mysql_real_escape_string

Http://ca.php.net/mysql_escape_string

Http://ca.php.net/addslasheshttp://pear.php.net/package-info.php?package=DB

5.6 Input data validation: Although data validation is provided at the client layer for user convenience, data validation must always be performed at the server layer. Client-side validation is inherently unsafe because these validations can be easily bypassed, for example, by disabling Javascript. A good design typically requires a WEB application framework to provide a server-side utility routine that validates the following: Required fields, field data types (by default, all HTTP request parameters are "strings"), field lengths, field ranges, field options, field patterns, cookie values, HTTP responses, and so on, the following sections describe some examples of checks.

PHP Example to validaterequired fields

Functionvalidaterequired ($input) {

...

$pass = false;

if (strlen (Trim ($input)) >0) {

$pass = true;

}

return $pass;

...

}

...

if (validaterequired ($fieldName)) {

FieldName is valid,continue processing request

...

}

J2ee

* * Pre-compiled statements:

Here are three possible ways to protect your application from SQL injection (that is, malicious tampering with SQL parameters). Instead of building SQL statements dynamically, use the following methods:

5.7 PreparedStatement, precompiled and stored in the PreparedStatement object pool. PreparedStatement defines setter methods to register input parameters that are compatible with supported JDBC SQL data types. For example, setString should be used for the input parameters of a VARCHAR or LongVarChar type (see the Java API for further details). Setting an input parameter in this way prevents an attacker from manipulating an SQL statement by injecting an error character, such as a single quotation mark.

Examples of how to use PreparedStatement in a Java EE:

Java preparedstatemenet Example

Get a connection to the database

Connection myconnection;

if (isdatasourceenabled ()) {

Using the DataSource to get a managed connection

Context CTX = new InitialContext ();

MyConnection = ((DataSource) ctx.lookup (DataSourceName)). getconnection (Dbusername, Dbpassword);

}else {

try {

Using the DriverManager to get a JDBC connection

Class.forName (Jdbcdriverclasspath);

MyConnection = Drivermanager.getconnection (Jdbcurl, Dbusername,dbpassword);

} catch (ClassNotFoundException e) {

...

}

}

...

try{

PreparedStatement mystatement =myconnection.preparestatement ("SELECT * from users where username =?");

Mystatement.setstring (1, Usernamefield);

ResultSet rs = Mystatement.executequery ();

...

Rs.close ();

}catch (SQLException SQLException) {

...

}finally {

Mystatement.close ();

Myconnection.close ();

}

5.8 CallableStatement, extending PreparedStatement to execute database SQL stored procedures. This class inherits the input setter method of the PreparedStatement (see [1] above).

The following example assumes that the database stored procedure has been created:

CREATE PROCEDURE Select_user (@usernamevarchar) as SELECT * from USERS WHERE USERNAME = @username;

How to use CallableStatement in a Java EE to perform an example of the above stored procedure:

Java preparedstatemenet Example

Get a connection to the database

Connection myconnection;

if (isdatasourceenabled ()) {

Using the DataSource to get a managed connection

Context CTX = new InitialContext ();

MyConnection = ((DataSource) ctx.lookup (DataSourceName)). getconnection (Dbusername, Dbpassword);

}else {

try {

Using the DriverManager to get a JDBC connection

Class.forName (Jdbcdriverclasspath);

MyConnection = Drivermanager.getconnection (Jdbcurl, Dbusername,dbpassword);

} catch (ClassNotFoundException e) {

...

}

}

...

try{

PreparedStatement mystatement = Myconnection.preparecall ("{= Callselect_user?,?}");

Mystatement.setstring (1, Usernamefield);

Mystatement.registeroutparameter (1, Types.varchar);

ResultSet rs = Mystatement.executequery ();

...

Rs.close ();

}catch (SQLException SQLException) {

...

}finally {

Mystatement.close ();

Myconnection.close ();

}

5.9 Entity Bean that represents the EJB business object in the persistent storage mechanism. There are two types of entity beans: Bean management and container management. When using bean-managed persistence, the developer is responsible for composing the SQL code that accesses the database. When you use container-managed persistence, the EJB container automatically generates SQL code. Therefore, the container is responsible for preventing malicious attempts to tamper with the generated SQL code.

Examples of how to use entity beans in a Java EE:

Java EE EJB Example

try{

Lookup The User Home interface

Userhome userhome = (userhome) context.lookup (User.class);

Find the User remote interface

User = Userhome.findbyprimarykey (new UserKey (Usernamefield));

...

}catch (Exception e) {

...

}

Recommended JAVA tools are not applicable

Reference Http://java.sun.com/j2se/1.4.1/docs/api/java/sql/PreparedStatement.htmlhttp://java.sun.com/j2se/1.4.1/docs /api/java/sql/callablestatement.html

SQL Injection Vulnerability Explanation