How Do Parameterized Queries Help Against 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.

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.

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.

Are Parameters really enough to prevent Sql injections?

Placeholders are enough to prevent injections. You might still be open to buffer overflows, but that is a completely different flavor of attack from an SQL injection (the attack vector would not be SQL syntax but binary). Since the parameters passed will all be escaped properly, there isn't any way for an attacker to pass data that will be treated like "live" SQL.

You can't use functions inside placeholders, and you can't use placeholders as column or table names, because they are escaped and quoted as string literals.

However, if you use parameters as part of a string concatenation inside your dynamic query, you are still vulnerable to injection, because your strings will not be escaped but will be literal. Using other types for parameters (such as integer) is safe.

That said, if you're using use input to set the value of something like security_level, then someone could just make themselves administrators in your system and have a free-for-all. But that's just basic input validation, and has nothing to do with SQL injection.

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.

Mysqli - Are parameterized queries enough for preventing XSS second order attacks?

Nope.

A parametrized query protects against SQL Injection; that is it ensures query parameters are well formed and correctly escaped prior to processing by the SQL back end.

XSS is a different class of problem whereby input should be sanitized of HTML markup; given that a correctly parametrized SQL value can still contain markup, you need additional encoding (E.g. htmlspecialchars()).

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
}

---

Related Topics