C# - Protecting against SQL Injection

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+''''

EXECUTE sp_executesql @sql