Protecting
against SQL Injection
Good Ways To Prevent SQL Injection
ORM
Use
Entity Framework for communication between C# and your SQL database. That will
make parameterized SQL strings that isn't vulnerable to SQL injection. Your
risk is reduced your risk simply by using an ORM like Linq2Entities, Linq2SQL,
NHibrenate.
Paramaterized
Queries
By using the SqlCommand and its child collection of parameters all the pain of checking for SQL injection is taken away from you and will be handled by these classes.
Example 1
private static void UpdateDemographics(Int32 customerID,
string demoXml, string
connectionString)
{
// Update the
demographics for a store, which is stored
// in an xml
column.
string commandText = "UPDATE Sales.Store
SET Demographics = @demographics "
+ "WHERE
CustomerID = @ID;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(commandText,
connection);
command.Parameters.Add("@ID", SqlDbType.Int);
command.Parameters["@ID"].Value = customerID;
// Use
AddWithValue to assign Demographics.
// SQL Server
will implicitly convert strings into XML.
command.Parameters.AddWithValue("@demographics", demoXml);
try
{
connection.Open();
Int32 rowsAffected = command.ExecuteNonQuery();
Console.WriteLine("RowsAffected: {0}", rowsAffected);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
Example 2
For example, a secure way of running a SQL query in JDBC using a parameterized statement would be:
// Define which user we want to find.
String
email = "user@email.com";
// Connect to the database.
Connection
conn = DriverManager.getConnection(URL, USER, PASS);
Statement
stmt = conn.createStatement();
// Construct the SQL statement we want to run, specifying the
parameter.
String
sql = "SELECT * FROM users WHERE email = ?";
// Run the query, passing the 'email' parameter value...
ResultSet
results = stmt.executeQuery(sql, email);
while
(results.next()) {
// ...do something with
the data returned.
}
Contrast
this to explicit construction of the SQL string, which is very, very dangerous:
// The user we want to find.
String
email = "user@email.com";
// Connect to the database.
Connection
conn = DriverManager.getConnection(URL, USER, PASS);
Statement
stmt = conn.createStatement();
// Bad, bad news! Don't construct the query with string
concatenation.
String
sql = "SELECT * FROM users WHERE email = '" + email + "'";
// I have a bad feeling about this...
ResultSet
results = stmt.executeQuery(sql);
while
(results.next()) {
// ...oh look, we got
Example 3
Stored
Procedure
CREATE PROCEDURE SP_GetLogin @username varchar(100), @password
varchar(100) AS
DECLARE @sql nvarchar(4000)
SELECT @sql = ' SELECT * FROM users' +
' FROM Product
Where username = ''' +
@username + ''' AND password = '''+@password+''''
@username + ''' AND password = '''+@password+''''
EXECUTE sp_executesql @sql
