2

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

3 Answers 3

2

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();
Sign up to request clarification or add additional context in comments.

1 Comment

I have edited my question to include all the code I have. My only problem is how to change Code.replace in the statement
1

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

  1. DRY - you don't have to repeat the query in code, you can just call the execute method and pass in the appropriate parameters
  2. 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

2 Comments

Using parameters with SQL is not limited to stored procedures.
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
0

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.