Parameterized Queries (Oracle, SQLServer, OleDb)

In my opinion, the parameterized queries approach is always better than the traditional approach of joining string to build a dynamic SQL string.

This last one is susceptible to SQL injections attacks, and usually leads to data format problems, you have to worry about how to encode the parameter according to its type. (For instance, the string parameters must be enclosed with a single quote).

Unfortunately, each database vendor implements parameterized queries in different ways, and it took me sometime today to figure out how to use them in Oracle.

SQL server uses a named parameter approach, it does not matter the parameters order, but each parameter name requires a "@" prefix.

string sql = "SELECT * FROM Customers WHERE CustomerId = @CustomerId";

SqlCommand command = new SqlCommand(sql);

command.Parameters.Add(new SqlParameter("@CustomerId", System.Data.SqlDbType.Int));

command.Parameters["@CustomerId"].Value = 1;

Oracle uses a similar approach, but a different prefix, since it expects a ":" prefix.

string sql = "SELECT * FROM Customers WHERE CustomerId = :CustomerId";

OracleCommand command = new OracleCommand(sql);

command.Parameters.Add(new OracleParameter(":CustomerId", OracleType.Int32));

command.Parameters[":CustomerId"].Value = 1;

OleDB uses a sequential approach, in this case, the parameters order really matters. A parameter is identified by a character "?" in the SQL query.

string sql = "SELECT * FROM Customers WHERE CustomerId = ?";

OleDbCommand command = new OleDbCommand(sql);

command.Parameters.Add(new OleDbParameter("CustomerId", OleDbType.Integer));

command.Parameters["CustomerId"].Value = 1;

 

Any comment is welcome.

Pablo. 

9 Comments

  • We often use dynamically generated parameterized SQL when stored procs are not flexible enough to handle the possible permutations. However, sometimes parameterized SQL may have performance problem compared to Dynamic SQL.

  • I agree with Richard Ponton - there is absolutely no reason what-so-ever for using lame dynamic sqls like:

    "SELECT * FROM emp WHERE name = '" + Textbox1.Text +"'"

    This is never, ever necessary. Additionally, it is incredibly lame, because everything must become a string (like dates, numbers) and be parsed back into the type by the server.

    Note that if you want to build sql dynamically, that's fine.. BUILD A PARAMETERIZED QUERY DYNAMICALLY (c# pseudocode):

    strSQL = "SELECT * FROM TABLE WHERE 1=1 ";
    List tempParametersList = new List;

    if(nameCheckbox.Checked){
    strSQL = strSQL & " AND name = :NAME";
    tempParametersList.Add(new OracleParameter("NAME", nameTextBox.Text));
    }

    ...


    Yeah, youre allowed to build a parameterized dynamic sql - its immune from injection, flexible and can be cached for repeated use

  • It is all good about using parameterized queries.
    But what about the case wherein I need to have a single code-base(i.e. common methods) which needs to access different databases ? That is quoting the line from the article:
    "...each database vendor implements parameterized queries in different ways".
    Do we have any solution for such a scenario wherein I could see only dynamic SQL string creation.

  • thanks you so saved me at least ten-twenty lines of code

  • Those are not "queries", Mike.

    Rule of Thumb: Select-Insert-Delete-Update statements are called queries, even though "querying would mean just Selecting.

    These statements are those to be parametrized, and realize this is just for the WHERE clause.

    In general SqlStatements are categorized as DDL( Data Definition Language ) ones or DML(Data Modification Language ) ones. Search for those for further info.

  • Your article helped me and worked without error. Kinda surprising for something found on the InterWebs. Thanks and Nice jorb!

  • So maybe someone can help me with this: Can you parameterize the ORDER BY clause of a SELECT query using the command objects? From what I have researched, I'd guess you can't. For example, the following query:
    SELECT * FROM [Shippers] WHERE [CompanyName] LIKE ([@WhereCompanyName]) ORDER BY [@OrderPhone]
    With these parameters:
    Parameter(0) = 'Blah'
    Parameter(1) = 'Phone' (this is the name of the column)
    This returns w/o error, but the data is not ordered. However this:
    SELECT * FROM [Shippers] WHERE [CompanyName] LIKE ([@WhereCompanyName]) ORDER BY [Phone]
    With one parameter:
    Parameters(0) = 'Blah'
    Works. So I am thinking that you can't parameterize the ORDER BY. Can anyone verify this please?

  • I guess I should let a sleeping dog lie but I won't. Instead, let me add my 2 cents. Whoever said that most inhouse developers don't know what a parameterized query or stored procedure is needs to do one of two things: 1, get new inhouse developers or 2, stay off the Internet and train them.

    He states that he knows how to do dynamic queries that do not allow SQL Injection but he doesn't offer any examples. Easily said when he doesn't have to back it up. Always use stored procedures or parameterized queries. I always prefer stored procedures so my SQL code is in one place. On some apps where we go against 3rd party databases then we can't use a stored procedure because it may get deleted at any time by the 3rd party. In that case one might consider dynamic sql.

    Otherwise, thanks for a very helpful article, Pablo.

  • I completely agree with many of the comments here; "there is never an excuse not to use parametersied queries".

    That said, I forsee the exact same problem as Praveen; when developing a single code-base that utilises multiple providers.

    Short of using Reflection to switch the markers off the actual type supplied (not great), what can you do?

Comments have been disabled for this content.