Fixing SQL Injection in .NET and MS SQL

Posted by on May 27, 2011 0 Comment

Note: This post is part of our series on “How to Fix SQL Injection Vulnerabilities“. The series contains examples on how to fix SQL Injection Vulnerabilities in various programming languages.

An SQL Injection attack is a code injection attack when input from an attacker reaches one of your databases without any filteration or validation. As a result, a malicious user can execute Read / Write / Delete / Update query in your database. In addition to this he can also run system level commands. Listed below are examples on how to prevent SQL Injection in C#, VB.NET & ASP.NET

Parameterized Query

C#

string queryText = "SELECT * FROM Students WHERE [email protected]";
SqlCommand cmd = new SqlCommand(queryText, conn);
cmd.Parameters.Add("@City",City);
SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();
da.Fill(ds);
return ds;

VB.NET

Dim queryText As String = "SELECT * FROM Students WHERE [email protected]"
Dim cmd As SqlCommand = New SqlCommand(queryText, conn)
cmd.Parameters.Add("@City",City)
Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)

GetCustomersFromCountry = New DataSet
da.Fill(GetCustomersFromCountry)

ASP.NET

/*Parameterized Query*/

string commandText = "SELECT * FROM Customers WHERE [email protected]";
SqlCommand cmd = new SqlCommand(commandText, conn);
cmd.Parameters.Add("@CountryName",countryName);

Stored Procedure

var connect = ConfigurationManager.ConnectionStrings["NorthWind"].ToString();
var query = "GetProductByID";

using (var conn = new SqlConnection(connect))
{
  using (var cmd = new SqlCommand(query, conn))
  {
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@ProductID", SqlDbType.Int).Value = Convert.ToInt32(Request["ProductID"]);
    conn.Open();
    //Process results
  }
}

Fixing SQL Injection in PHP and MS SQL

Posted by on May 27, 2011 0 Comment

Note: This post is part of our series on “How to Fix SQL Injection Vulnerabilities“. The series contains examples on how to fix SQL Injection Vulnerabilities in various programming languages.

An SQL Injection attack is a code injection attack when input from an attacker reaches one of your databases without any filteration or validation. As a result, a malicious user can execute Read / Write / Delete / Update query in your database. In addition to this he can also run system level commands. PHP for example has simple ways in preventing these attacks by filtering user inputs. Some of them are listed below:

Parameterized Query

$qualification = $_POST[‘quatification’];
$sex = $_POST[‘sex’];

$sql = "SELECT Name, City, Phone FROM Students WHERE Qualification = ? AND Sex = ?; 

$params = array($qualification, $sex);
$result = sqlsrv_query($conn, $sql, $params);

Here sqlsrv_query function accepts three parameters: $conn, $sql, $params (optional):

  • $conn – Connection to the SQL Server Database
  • $sql – The SQL query to fetch the data. Question marks (?) are used as placeholders for parameters.
  • $params – Array values that correspond to the placeholders (?) in the SQL query.

Stored Procedure

In the example below a stored procedure is created and the output is binded together with the variable using mssql_bind command.

// Create a new stored prodecure
$stmt = mssql_init('NewUserRecord');

// Bind the field names
mssql_bind($stmt, '@username',  'sameer',  SQLVARCHAR,  false,  false,  60);
mssql_bind($stmt, '@name',      'Sameer',  SQLVARCHAR,  false,  false,  60);
mssql_bind($stmt, '@age',       25,       SQLINT1,     false,  false,   3);

// Execute
mssql_execute($stmt);

Fixing SQL Injection in Java and MS SQL

Posted by on May 26, 2011 1 Comment

Note: This post is part of our series on “How to Fix SQL Injection Vulnerabilities“. The series contains examples on how to fix SQL Injection Vulnerabilities in various programming languages.

An SQL Injection attack is a code injection attack when input from an attacker reaches one of your databases without any filteration or validation. As a result, a malicious user can execute Read / Write / Delete / Update query in your database. In addition to this he can also run system level commands. The following example shows how SQL Injection can be prevented by in Java

Parameterized query

String selectStatement = "SELECT * FROM User WHERE userId = ? ";
PreparedStatement prepStmt = con.prepareStatement(selectStatement);
prepStmt.setString(1, userId);
ResultSet rs = prepStmt.executeQuery();

Fixing SQL injection in ASP and MS SQL

Posted by on May 26, 2011 0 Comment

Note: This post is part of our series on “How to Fix SQL Injection Vulnerabilities“. The series contains examples on how to fix SQL Injection Vulnerabilities in various programming languages.

An SQL Injection attack is a code injection attack when input from an attacker reaches one of your databases without any filteration or validation. As a result, a malicious user can execute Read / Write / Delete / Update query in your database. In addition to this he can also run system level commands. The following example shows how to prevent a malicious input in ASP by filtering user input before it is passed to MS SQL.

Parameterized query

string commandText = "SELECT * FROM Customers WHERE [email protected]";
SqlCommand cmd = new SqlCommand(commandText, conn);
cmd.Parameters.Add("@Location",Location);

Validating User input

string Lastname = this.lastnameTb.Text.Replace("'", "''");
string sql = "Update Users SET Lastname=' "+ Lastname +"'  WHERE id="+userID;