Parameterized Queries VS. SQL Injection

How do parameterized queries help against SQL injection?

Parameterized queries do proper substitution of arguments prior to running the SQL query. It completely removes the possibility of "dirty" input changing the meaning of your query. That is, if the input contains SQL, it can't become part of what is executed because the SQL is never injected into the resulting statement.

parameterized queries vs. SQL injection

This works but would still be vulnerable to injections right?

Yeah, your code is terrifyingly vulnerable to SQL injections.

I know that I should be using parameterized queries to avoid SQL injections.

Oh absolutely yeah.

My question is, how can I do this when I'm passing the query as a string parameter?

You simply shouldn't be passing the query as a string parameter. Instead you should be passing the query as string parameter containing placeholders and the values for those placeholders:

public static DataTable SqlDataTable(string sql, IDictionary<string, object> values)
{
    using (SqlConnection conn = new SqlConnection(DatabaseConnectionString))
    using (SqlCommand cmd = conn.CreateCommand())
    {
        conn.Open();
        cmd.CommandText = sql;
        foreach (KeyValuePair<string, object> item in values)
        {
            cmd.Parameters.AddWithValue("@" + item.Key, item.Value);
        }

        DataTable table = new DataTable();
        using (var reader = cmd.ExecuteReader())
        {
            table.Load(reader);
            return table;
        }
    }
}

and then use your function like this:

DataTable dt = SqlComm.SqlDataTable(
    "SELECT * FROM Users WHERE UserName = @UserName AND Password = @Password",
    new Dictionary<string, object>
    {
        { "UserName", login.Text },
        { "Password", password.Text },
    }
);

if (dt.Rows.Count > 0)
{
   // do something if the query returns rows
}

Why does using parameterized queries or entity framework prevent sql injection?

Your first example is parameterised and is not vulnerable to SQL injection.

Parameterised queries aren't simply replaced by the server with values (like you might do manually replacing @var with value). They are sent and received exactly as you sent it.. with @valueFromApplication.

The server will parse the query.. and when it gets to a variable it will look up the value supplied. If that value is '' ; DROP DATABASE Foo --.. then that becomes the value it uses. It doesn't parse that.. it just uses it as text/number/whatever type it is.

To add about Entity Framework, it internally uses Parameterised query so it is also SQL injection safe.

How does SQLParameter prevent SQL Injection?

Basically, when you perform a SQLCommand using SQLParameters, the parameters are never inserted directly into the statement. Instead, a system stored procedure called sp_executesql is called and given the SQL string and the array of parameters.

When used as such, the parameters are isolated and treated as data, instead of having to be parsed out of the statement (and thus possibly changing it), so what the parameters contain can never be "executed". You'll just get a big fat error that the parameter value is invalid in some way.

Can parameterized statement stop all SQL injection?

The links that I have posted in my comments to the question explain the problem very well. I've summarised my feelings on why the problem persists, below:

1. Those just starting out may have no awareness of SQL injection.

2. Some are aware of SQL injection, but think that escaping is the (only?) solution. If you do a quick Google search for php mysql query, the first page that appears is the mysql_query page, on which there is an example that shows interpolating escaped user input into a query. There's no mention (at least not that I can see) of using prepared statements instead. As others have said, there are so many tutorials out there that use parameter interpolation, that it's not really surprising how often it is still used.

3. A lack of understanding of how parameterized statements work. Some think that it is just a fancy means of escaping values.

4. Others are aware of parameterized statements, but don't use them because they have heard that they are too slow. I suspect that many people have heard how incredibly slow paramterized statements are, but have not actually done any testing of their own. As Bill Karwin pointed out in his talk, the difference in performance should rarely be used as a factor when considering the use of prepared statements. The benefits of prepare once, execute many, often appear to be forgotten, as do the improvements in security and code maintainability.

5. Some use parameterized statements everywhere, but with interpolation of unchecked values such as table and columns names, keywords and conditional operators. Dynamic searches, such as those that allow users to specify a number of different search fields, comparison conditions and sort order, are prime examples of this.

6. False sense of security when using an ORM. ORMs still allow interpolation of SQL statement parts - see 5.

7. Programming is a big and complex subject, database management is a big and complex subject, security is a big and complex subject. Developing a secure database application is not easy - even experienced developers can get caught out.

8. Many of the answers on stackoverflow don't help. When people write questions that use dynamic SQL and parameter interpolation, there is often a lack of responses that suggest using parameterized statements instead. On a few occasions, I've had people rebut my suggestion to use prepared statements - usually because of the perceived unacceptable performance overhead. I seriously doubt that those asking most of these questions are in a position where the extra few milliseconds taken to prepare a parameterized statement will have a catastrophic effect on their application.

How parameterized queries/prepared statements better in SQL injection than escaping user input

Q: Can you give an example that parameterized query prevent the SQL injection attack when a user input to the query still contains a special character to cause harm?

A: There have been some multibyte character exploits in code that doesn't properly account for character sets, resulting in holes in the escaping mechanism. (Where the "escape string" thinks it's working on a string in particular encoding, but the actual bytes are in a different encoding, and sneakily sliding single quotes into the SQL text.)

But I don't really think that's the strongest argument for prepared statements with bind placeholders.

A strong argument is that when we look at the code, we see static SQL text, not dynamically generated...

 $sql = 'SELECT fee, fi FROM fo WHERE fum = ?'; 
 $dbh->prepare($sql);

We see that code, and we look at that SQL text... and we recognize immediately, there is no way that the SQL text is going to be other than what we see. We don't have to look anywhere else in the code; we see it right there on two lines.

When we see this:

 $sql = "SELECT fee, fi FROM fo WHERE fum = $fumval";

That's double quotes, there's variable interpretation going on. Is $fumval guaranteed to be safe for inclusion in the SQL text, where did $fumval come from? Should there be single quotes around $fumval, or are we guaranteed that it's already enclosed in single quotes?

Okay, maybe there's a line right before that:

 $fumval = $dbh->quote($unsafe_fumval);

If that line isn't right above the generation of the SQL text, we need to go check... are we guaranteed the $fumval is safe?

The point is this... the SQL is being dynamically constructed. It might be better if it was done like this:

$sql = 'SELECT fee, fi FROM fo WHERE fum = ' . $dbh->quote($unsafe_fumval);

For a simple statement, maybe it's six of one and half a dozen of the other. But when SQL statements get larger, involving multiple tables and dozens of column references and values, the dynamic construction gets harder to verify that there are not any problems in it.

Is it possible to write secure code using dynamically generated SQL and "escape string" processing on values? YES.

Is it possible to write vulnerable code that uses prepared statements with dynamically generated SQL text? YES.

---

It's really the pattern of static SQL text, passing values provided through bind placeholders is what gets us the bang for our buck... code that is written in a way in which we can identify as not vulnerable to SQL injection.

disadvantage of parameterized query to overcome SQLInjection?

It is always a good idea to apply all the techniques we know to prevent injection.
The "pros" of paramtized queries are primarily avoiding "OR 1=1" and other common SQL injections: you force the database to interpret everything that comes within the bound variables as data and not as SQL instructions. Thus it becomes very difficult for - say- me, if I have bad intentions, to drop one of your tables.

The "cons" of parametized SQL are mainly related to the fact that you don't have the flexibility you were used to (you can't make two queries at the same time, with two while loops, the second using or trying to use a parameter obtained with the first call, without some adjustments, in MySQLi, for instance. Examples of the problems you may find when switching are as in Nested looping with mysqli and fetch_object, or as stated here). This is not a good reason to avoid switching to a safer programming, and I think it also prevents some bad habits one may have acquired with the so-called "vanilla programming" (as for me, I had acquired some), but, as the other person answering this question points out, there are workarounds to solve that as well. I am not aware of other cons, maybe because there aren't any.

You could also refer to related questions on SO, such as Are Parameters really enough to prevent Sql injections? and many more. I am not sure that this isn't a duplicate, but am replying all the same to try to clear your doubts.

What is parameterized query?

A parameterized query (also known as a prepared statement) is a means of pre-compiling a SQL statement so that all you need to supply are the "parameters" (think "variables") that need to be inserted into the statement for it to be executed. It's commonly used as a means of preventing SQL injection attacks.

You can read more about these on PHP's PDO page (PDO being a database abstraction layer), although you can also make use of them if you're using the mysqli database interface (see the prepare documentation).

SQL Injection with Parameterized Queries in ASP.NET

You're really close - you have parameterized the values you are updating with, but not the values in your WHERE clause. Try something like this instead:

SqlConnection Conn = new SqlConnection(@"Data Source=**********;Initial Catalog=********;Persist Security Info=True;User ID=******;Password=*********");
SqlCommand updateMeeting = new SqlCommand(@"
    UPDATE [*******].[dbo].[**********] 
    SET Title=@title, 
        Date=@date, 
        Location=@location, 
        Announcement=@announcement 
    WHERE Title = @commands1
        AND Date = @commands2
        AND Location = @commands3",
        Conn);
updateMeeting.Parameters.AddWithValue("@title", newTitle);
updateMeeting.Parameters.AddWithValue("@date", newDate);
updateMeeting.Parameters.AddWithValue("@location", newLocation);
updateMeeting.Parameters.AddWithValue("@announcement", newBody);
updateMeeting.Parameters.AddWithValue("@commands1", commands[1]);
updateMeeting.Parameters.AddWithValue("@commands2", Convert.ToDateTime(commands[2]));
updateMeeting.Parameters.AddWithValue("@commands3", commands[3]);
updateMeeting.Connection.Open();
updateMeeting.ExecuteNonQuery();
updateMeeting.Connection.Close();

---

Related Topics