how to change sql statement to parameterized query?

Question

I have an sql query that I need change to parameters so I can avoid sql injection.

adapter.SelectCommand.CommandText = @"SELECT c.*,(Select Initials FROM users WHERE User_ID = c.CreatedByUser) AS CreatedBy, (SELECT Initials FROM users WHERE User_ID = c.ModifiedByUser) AS ModifiedBy FROM currency c WHERE c.Company_ID = " + Company_ID + " AND c.CurrencyCode = '" + Code.Replace("'", "''") + "' ORDER BY c.Description
adapter.SelectCommand.Parameters.Add(new MySqlParameter("company_ID", Company_ID));
adapter.SelectCommand.Parameters.Add(new MySqlParameter("code", Code));

I know for Company_ID I need to change it to WHERE c.Company_ID = ?company_ID but I am not sure what to do for c.CurrencyCode = '" + Code.Replace("'", "''") + "'

I just don't know how to change the Code.Replace part, since its not a simple as company_ID

score 2 · Answer 1 · edited May 23 '17 at 11:51

As per here

Try using (for odbc for example):

cmd.Parameters.Add("?CURRENCY", OdbcType.VarChar, Code.Replace("'", "''"))

Odbc approach

OdbcCommand cmd = sql.CreateCommand();
cmd.CommandText = "SELECT UNIQUE_ID FROM userdetails WHERE USER_ID IN (?, ?)";
cmd.Parameters.Add("?ID1", OdbcType.VarChar, 250).Value = email1;
cmd.Parameters.Add("?ID2", OdbcType.VarChar, 250).Value = email2;

For oracle:

//create SQL and insert parameters
OracleCommand cmd = new OracleCommand("insert into daily_cdr_logs (message) values (:_message)", con);
cmd.Parameters.Add(new OracleParameter("_message", msg));

For mysql:

cmd = new MySqlCommand("SELECT * FROM admin WHERE admin_username=@val1 AND admin_password=PASSWORD(@val2)", MySqlConn.conn);
cmd.Parameters.AddWithValue("@val1", tboxUserName.Text);
cmd.Parameters.AddWithValue("@val2", tboxPassword.Text);
cmd.Prepare();

I have edited my question to include all the code I have. My only problem is how to change Code.replace in the statement — user2026041, May 28 '15 at 13:43

Pseudonym · Answer 2 · 2015-05-28T13:40:58.457

So a parameterized query (to me at least) generally means that you have created a stored procedure on your database and then use your code to execute the stored procedure while passing in the relevant parameters.

This has a couple of benefits

DRY - you don't have to repeat the query in code, you can just call the execute method and pass in the appropriate parameters
Helps prevent SQL injection - You can only modify the parameters which hopefully will be sanitized before being passed to the query

Here is how to create a stored procedure according to MSDN

and

Here is how to execute a a stored procedure according to MSDN

If you are determined to do it via LINQ, MSDN has what you are looking for here

EDIT: It seems you are concerned about sql-injection (which is good!), here is an article (again from MSDN) that covers that topic pretty extensively

Using parameters with SQL is not limited to stored procedures. — juharr, May 28 '15 at 13:38
Very true, I actually specifically noted that *to me* a parameterized query was defined as using a sproc in a database, however the last link in the post shows some other methods of using things like LINQ to make parameterized queries — Pseudonym, May 28 '15 at 13:39

score 0 · Accepted Answer · answered May 28 '15 at 13:54

0

I have the answer. c.CurrencyCode = '" + Code.Replace("'", "''") + "' simply changes to c.CurrencyCode = ?code

answered May 28 '15 at 13:54

user2026041

131
2
3
11

how to change sql statement to parameterized query?

3 Answers3

Linked