SQL Injection
SQL Injection
One code injection method that could wipe out your database is SQL injection.
One of the most popular methods for web hacking is SQL injection.
The act of inserting harmful code into SQL statements through web page input is known as SQL injection.
SQL in Web Pages
When you ask a user for input, such as their username/userid, and they respond with a SQL statement that you will unknowingly execute on your database, this is known as SQL injection.
Take a look at the following example, which adds a variable (txtUserId) to a select string to generate a SELECT query. The variable (getRequestString) is retrieved from user input:
Example
txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
The rest of this chapter describes the potential dangers of using user input in SQL statements.
SQL Injection Based on 1=1 is Always True
Reexamine the example from above. The code’s initial goal was to write a SQL statement that would choose a user based on their user ID.
A user may enter some “smart” input, such as this, if nothing stops them from entering “wrong” input.
—– LIVE TEXT FILD MUKAVU —–
Then, the SQL statement will look like this:
SELECT * FROM Users WHERE UserId = 105 OR 1=1;
Because OR 1=1 is always TRUE, the SQL above is legal and will return ALL data from the “Users” table.
Does the aforementioned example appear risky? What happens if passwords and names are stored in the “Users” table?
This SQL statement and the one above are somewhat similar:
SELECT UserId, Name, Password FROM Users WHERE UserId = 105 or 1=1;
If a hacker were to type 105 OR 1=1 into the input area, they might potentially gain access to every user name and password in the database.
SQL Injection Based on ""="" is Always True
Here is an example of a user login on a web site:
----- LIVE TEXT FILD MUKAVU -----
Example
uName = getRequestString("username");
uPass = getRequestString("userpassword");
sql = 'SELECT * FROM Users WHERE Name ="' + uName + '" AND Pass ="' + uPass + '"'
Result
SELECT * FROM Users WHERE Name ="John Doe" AND Pass ="myPass"
A hacker might get access to user names and passwords in a database by simply inserting OR “”=”” into the user name or password text box:
SQL Injection Based on Batched SQL Statements
Batched SQL statements are supported by most databases.
A set of two or more SQL statements, divided by semicolons, is referred to as a batch of SQL statements.
The “Suppliers” table will be deleted once the SQL statement below returns every row from the “Users” table.
Example
SELECT * FROM Users; DROP TABLE Suppliers
Look at the following example:
Example
txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
And the following input:
—– LIVE TEXT FILD MUKAVU —–
The valid SQL statement would look like this:
Result
SELECT * FROM Users WHERE UserId = 105; DROP TABLE Suppliers;
Use SQL Parameters for Protection
SQL parameters can be used to prevent SQL injection on a website.
SQL parameters are controlled values that are added to a SQL query during execution.
ASP.NET Razor Example
txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = @0";
db.Execute(txtSQL,txtUserId);
Keep in mind that a @ symbol in the SQL statement indicates a parameter.
Each argument is checked by the SQL engine to make sure it is valid for its column and is handled literally, not as a component of the SQL that has to be run.
Another Example
txtNam = getRequestString("CustomerName");
txtAdd = getRequestString("Address");
txtCit = getRequestString("City");
txtSQL = "INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)";
db.Execute(txtSQL,txtNam,txtAdd,txtCit);
Examples
The examples that follow demonstrate how to create parameterized queries in a few popular web languages.
SELECT STATEMENT IN ASP.NET:
txtUserId = getRequestString("UserId");
sql = "SELECT * FROM Customers WHERE CustomerId = @0";
command = new SqlCommand(sql);
command.Parameters.AddWithValue("@0",txtUserId);
command.ExecuteReader();
INSERT INTO STATEMENT IN ASP.NET:
txtNam = getRequestString("CustomerName");
txtAdd = getRequestString("Address");
txtCit = getRequestString("City");
txtSQL = "INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)";
command = new SqlCommand(txtSQL);
command.Parameters.AddWithValue("@0",txtNam);
command.Parameters.AddWithValue("@1",txtAdd);
command.Parameters.AddWithValue("@2",txtCit);
command.ExecuteNonQuery();
INSERT INTO STATEMENT IN PHP:
$stmt = $dbh->prepare("INSERT INTO Customers (CustomerName,Address,City)
VALUES (:nam, :add, :cit)");
$stmt->bindParam(':nam', $txtNam);
$stmt->bindParam(':add', $txtAdd);
$stmt->bindParam(':cit', $txtCit);
$stmt->execute();
