Task 4: Countermeasure — Prepared Statement The fundamental problem of the SQL injection vulnerability is the failure to separate code from data. When constructing a SQL statement, the program (e.g. PHP program) knows which part is data and which part is code. Unfortunately, when the SQL statement is sent to the database, the boundary has disappeared; the boundaries that the SQL interpreter sees may be different from the original boundaries that was set by the developers. To solve this problem, it is important to ensure that the view of the boundaries are consistent in the server-side code and in the database. The most secure way is to use prepared statement. Figure 3: Prepared Statement Workflow To understand how prepared statement prevents SQL injection, we need to understand what happens when SQL server receives a query. The high-level workflow of how queries are executed is shown in SEED Labs/adapted by Walter Figure 3. In the compilation step, queries first go through the parsing and normalization phase, where a query is checked against the syntax and semantics. The next phase is the compilation phase where keywords (e.g. SELECT, FROM, UPDATE, etc.) are converted into a format understandable to machines. Basically, in this phase, query is interpreted. In the query optimization phase, the number of different plans are considered to execute the query, out of which the best optimized plan is chosen. The chosen plan is stores in the cache, so whenever the next query comes in, it will be checked against the content in the cache; if it's already present in the cache, the parsing, compilation and query optimization phases will be skipped. The compiled query is then passed to the execution phase where it is actually executed. Prepared statement comes into the picture after the compilation but before the execution step. A prepared statement will go through the compilation step, and be turned into a pre-compiled query with empty placeholders for data. To run this pre-compiled query, data need to be provided, but these data will not go through the compilation step; instead, they are plugged directly into the pre-compiled query, and are sent to the execution engine. Therefore, even if there is SQL code inside the data, without going through the compilation step, the code will be simply treated as part of data, without any special meaning. This is how prepared statement prevents SQL injection attacks. Here is an example of how to write an prepared statement in PHP. We use a SELECT statement in the following example. We show how to use prepared statement to rewrite the code that is vulnerable to SQL injection attacks. $conn = getDB(); $sql = "SELECT name, local, gender FROM USER_TABLE WHERE id = $id AND password ='$pwd' "; $result = $conn->query($sql)) The above code is vulnerable to SQL injection attacks. It can be rewritten to the following $conn = getDB(); $stmt = $conn->prepare("SELECT name, local, gender FROM USER_TABLE WHERE id = ? and password = ? "); // Bind parameters to the query $stmt->bind_param("is", $id, $pwd); $stmt->execute(); $stmt->bind_result($bind_name, $bind_local, $bind_gender); $stmt->fetch(); Using the prepared statement mechanism, we divide the process of sending a SQL statement to the database into two steps. The first step is to only send the code part, i.e., a SQL statement without the actual the data. This is the prepare step. As we can see from the above code snippet, the actual data are replaced by question marks (?). After this step, we then send the data to the database using bind_param(). The database will treat everything sent in this step only as data, not as code anymore. SEED Labs/adapted by Walter It binds the data to the corresponding question marks of the prepared statement. In the bind param() method, the first argument "is" indicates the types of the parameters: "i" means that the data in $id has the integer type, and "s" means that the data in $pwd has the string type. For this task, go to /var/www/seedlabsqlinjection.com/public_html folder on the web server.

Task 4.1: Identify the php file that is used for authentication. Identify the code snippet(s) in this file that is causing the SQL Injection vulnerability. Describe what needs to be done to correct this. Task 4.2: Identify the php file that is used for modifying the profile. Identify the code snippet(s) in this file that is causing the SQL Injection vulnerability. Describe what needs to be done to correct this.

$conn = getDB();

$sql = "SELECT name, local, gender

FROM USER_TABLE

WHERE id = $id AND password ='$pwd' ";

$result = $conn->query($sql))

The above code is vulnerable to SQL injection attacks. It can be rewritten to the following

$conn = getDB();

$stmt = $conn->prepare("SELECT name, local, gender

FROM USER_TABLE

WHERE id = ? and password = ? ");

// Bind parameters to the query

$stmt->bind_param("is", $id, $pwd);

$stmt->execute();

$stmt->bind_result($bind_name, $bind_local, $bind_gender);

$stmt->fetch();

pur-new-sol

Related Questions