How to Add User-Supplied Input to an SQL Statement

User provided input SQL-escaping

In fact, the one and the only thing you should ever consider (we're not speaking of test projects here, obviously) is to use parameterized statements. This is the only way (when SQL synax allows for them, of course). Properly done, these won't make your system more complicated, but they will make it more robust.

sql delete statements with multiple where condition error

If your question is purely about SQL, then yes, what you have will work. But, as you have it, you have a very serious security problem. Google "SQL injection attacks". I'm not sure what you are using for data access (ADO.NET? Entiry Framework? Dapper?) But regardless, you'll want to use parameters:

var sql = "delete from favourite where username=@username and id=@id";

and then:

cmd.Parameters.AddWithValue("@username", Session["username"].ToString());
cmd.Parameters.AddWithValue("@id", id);

But even then, AddWithValue isn't the best way, because it can cause type conversion issues once the query hits the database. You are better off doing it longhand:

var userNameParam = new SqlParameter("username", SqlDbType.VarChar);
userNameParam.Value = Session["username"].ToString();

var idParam = new SqlParameter("id", SqlDbType.Int);
idParam .Value = id;

command.Parameters.Add(salaryParam);
command.Parameters.Add(idParam );

Simple user input sanitization

No it doesn't prevent it at all. It is used more so to prevent XSS attacks as explained by Microsoft here. Read this Stackoverflow question for some ideas on preventing SQL injection.

Depending on the environment you are in, I would use a technology such as the Entity Framework or NHibernate which prevents SQL injection altogether, so you do not even have to worry about it.

---

Related Topics