Start networking and exchanging professional insights

Register now or log in to join your professional community.

Follow

How to protect my site from sql injection ?

user-image
Question added by Mohamed Essam Hussien , Senior Full Stack engineer , Affinidi
Date Posted: 2013/03/01
Zaid Rabab'a
by Zaid Rabab'a , Software Development Team Leader , Al-Safa Co. Ltd.

The gist of it is that you should never trust user input, and user input includes everything that comes from the environment, including $_GET, $_POST, $_COOKIE, $_SESSION, even `$_SERVER.
In your code you blindly trust: $k = $_GET['k']; Then make a filter function to clear the special characters that will make sql injection

Hazem Qannash
by Hazem Qannash , Technical Team Leader , Bayt.com

You can use Binding or Escaping to protect your site from the sql injection.
Example (PHP): Unsafe sql: $sql = 'SELECT * FROM table WHERE name ='.
$name .
'AND value =' .
$value; Binding: $obj = $dbh->prepare('SELECT * FROM table WHERE name = ? AND value = ?'); $obj->execute(array($name, $value)); $res = $obj->fetchAll(); Escaping: $sql = sprintf('SELECT * FROM table WHERE name = %s AND value = %s', mysql_real_escape_string($name), mysql_real_escape_string($value)); for more information http://en.wikipedia.org/wiki/SQL_injection#Mitigation

Asif ahmed Syed
by Asif ahmed Syed , Lead SharePoint Application Developer , International Center for Sports Security

This task is related with security which is a bit difficult to implement. First of all you have to know about what SQL injection is and than you can implement it easily.

 

You have to follow these steps as,

1) Check for input constraints:

for that do not depend on only client side constraints use server side constraint like RegularExpressionValidator or RangeValidator etc.

e.g.

<%@ language="C#" %> <form id="form1" runat="server"> <asp:TextBox ID="SSN" runat="server"/> <asp:RegularExpressionValidator ID="regexpSSN" runat="server" ErrorMessage="Unidentified SSN Number" ControlToValidate="SSN" ValidationExpression="^\\d{3}-\\d{2}-\\d{4}$" /> </form>Code for Constraint input in data accesspublic void CreateNewUserAccount(string name, string password) { if ( !Regex.IsMatch(userIDTxt.Text, @"^[a-zA-Z'./s]{1,40}$")) throw new FormatException("Invalid name format"); if ( !Regex.IsMatch(passwordTxt.Text, @"^(?=.*\\d)(?=.*[a-z])(?=.*[A-Z]).{8,10}$" )) throw new FormatException("Invalid password format"); } 2) use SqlParameterCollection() asusing (SqlConnection con = new SqlConnection(connectionString)) { DataSet ds = new DataSet(); SqlDataAdapter SqlDA = new SqlDataAdapter( "LoginStoredProcedure", con); SqlDA.SelectCommand.CommandType = CommandType.StoredProcedure; SqlDA.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar,11); SqlDA.SelectCommand.Parameters["@au_id"].Value = SSN.Text; SqlDA.Fill(ds); }3) Use parameter Batchingusing (SqlConnection con = new SqlConnection(connectionString)) { SqlDataAdapter da = new SqlDataAdapter( "SELECT Emp_Name INTO #Temp1 FROM Employee " + "WHERE Emp_name > @empNameParm; SELECT CompanyName FROM Employee " + "WHERE Country = @countryParm and Emp_Name IN " + "(SELECT Emp_Name FROM #Temp1);", con); SqlParameter empNameParm = da.SelectCommand.Parameters.Add( "@empNameparm", SqlDbType.NChar,5); empNameParm.Value = Emp_Name.Text; SqlParameter countryParm = da.SelectCommand.Parameters.Add( "@countryParm", SqlDbType.NVarChar,15); countryParm.Value = country.Text; con.Open(); DataSet ds = new DataSet(); da.Fill(dataSet); }

Ibrahim Magdy
by Ibrahim Magdy , Senior Full Stack Software Developer , Trustwave

The answer is not straight forward You have to know SQL injection first Understand the technology or the way you are using SQL Whether you are using SQL commands, stored procedure or even ORM Each one of which has it's vulnerabilities and techniques to exploit, a b c measuring is input validation input validation can be by choosing the exact input valid string may be by using regex or by type restriction , another way is encoding data before storing it, if you encode the string into lets say base64 you make sure that injection is hard to be used in that field though it costs a lot of (storage, encoding and decoding) If you are using MVC it is a good practice to have strong typed models.. There are frameworks that have built in protection against such attacks, like Django .. One last thing though you might not have SQL attack but if you don't validate your input and outputs you may get other attacks like cross site scripting One again using strong types and ensure you know the framework you work with and it's mitigation mechanisms output encoding for example for HTML ... Etc can help you to avoid these attacks

SANDEEP KAUSHIK
by SANDEEP KAUSHIK , Software Developer , The brainhub | Technology

try to use sql procedure as much as u can ... caoz there is no chance of sql injection in proceduew parameters 

You should use sqlParameter to prevent from sql injection

SQL

select * from tablename where fieldname=@filedname

 

You should use OracleParameter to prevent from sql injection

Oracle

select * from tablename where fieldname=:filedname

 

how to apply in .Net 

 

string sql = "select * from tablename where fieldname=@filedname";

SqlConnection connection = new SqlConnection(/* connection info */);

SqlCommand command = new SqlCommand(sql, connection);

 

command.Parameters.AddWithValue("fieldname", fieldname.Text);

 

 

Mohammed Osman
by Mohammed Osman , Senior Software Engineer , Thiqah Business Solutions

Use stored procedures. Using stored procedures is more secure and fast.

Rami Zebian
by Rami Zebian , CEO , LeLaboDigital

in .Net, you can use: input.replace(" ' ", " '' ") to replace the single quote by 2 single quotes on any method that interacts with the DB

wasseem Abu-zaghlan
by wasseem Abu-zaghlan , Senior Technical consultant , Method

make a input validation over special characters that will make sql injection such as : <> ! '

in MVC.net you can create custom validation and use it as action method annotations.

More Questions Like This