Insert into statement with file path

Question

string SqlStr = string.Format("insert into O_Tracker " +
           "(order_id,client_name,job_name,note_,s_date,e_date,paid,pickup_status,sub_orders) values " +
           "({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')"      
           ,s.Order_Id, s.Client_Name, s.Job_Name, s.Note, s.Start_Date,
           s.End_Date, s.Paid, s.Ready2Pickup, subOrdersPath);

I keep getting this error from the debugger:

Syntax error (missing operator) in query expression

I just can't figure it out what's wrong in here. thanks for your help in advance.

UPDATE: I've changed the code to a format with parameters and got "mismatch criteria expression access" error.

string SqlStr ="insert into O_Tracker " + "(order_id,client_name,job_name,note_,s_date,e_date,paid,pickup_status,sub_orders) values(@order_id,@client_name,@job_name,@note_,@s_date,@e_date,@paid,@pickup_status,@sub_orders)";

        OleDbCommand cmd = new OleDbCommand();
        cmd.Parameters.AddWithValue("@order_id", s.Order_Id);
        cmd.Parameters.AddWithValue("@client_name", s.Client_Name);
        cmd.Parameters.AddWithValue("@job_name", s.Job_Name);
        cmd.Parameters.AddWithValue("@note_", s.Note);
        cmd.Parameters.AddWithValue("@s_date", s.Start_Date);
        cmd.Parameters.AddWithValue("@e_date", s.End_Date);
        cmd.Parameters.AddWithValue("@paid", s.Paid);
        cmd.Parameters.AddWithValue("@pickup_status", s.Ready2Pickup);
        cmd.Parameters.AddWithValue("@sub_orders", subOrdersPath);

        try
        {             
            cnn.Open();
            cmd.Connection = cnn;
            cmd.CommandText = SqlStr; 
            cmd.ExecuteNonQuery();
        }
        catch (OleDbException ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {              
            cnn.Close();
        }

Summary

Had a problem to add to a specific table field a file path. the guys here noticed that my code is vulnerable to SQL injection so i've done as their suggested and fixed my code.

another problem occurred after that and simply fixed it because the date field were not match.

Thanks all for you help! :)

Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — mjwills, Feb 19 '18 at 10:27
What is the **exact** value of `SqlStr` when the exception occurs? — mjwills, Feb 19 '18 at 10:27
@mjwills `"insert into O_Tracker (order_id,client_name,job_name,note_,s_date,e_date,paid,pickup_status,sub_orders) values (1001,'Example','gssgsgsgsgs','אין הערות','19/02/2018 12:21:36','19/02/2018 12:21:36','לא','לא','C:\\Users\\SuperUser\\Dropbox\\My Project\\My Project\\bin\\Debug\\\\Orders\\1001gssgsgsgsgs.txt')"` — M. rit, Feb 19 '18 at 10:29
Use parameters in your SqlCommand. You shouldn't send formatted strings like this. — Steve Harris, Feb 19 '18 at 10:32
@MongZhu its a typo of mixing hebrew and english in here. the empty one contains the לא after the 12:21:36. in the debugger everything looks fine. when i change subOrdersPath to just "sample text" it works. — M. rit, Feb 19 '18 at 10:36
@SteveHarris i send it to a method that send it like a regular sql query. can you explain me what's wrong with it? — M. rit, Feb 19 '18 at 10:39
You should really lookup how to use Sql Parameters, this code is not safe. For help with your query, copy paste the 'SqlStr' and show us what it's like. — Carra, Feb 19 '18 at 10:43
What happens when subOrdersPath = "'); DROP TABLE O_Tracker--"? Sql injection just killed your database! — Steve Harris, Feb 19 '18 at 10:44
how comes that you have an non existing folder in here: `Debug\\\\Orders` ? what happens if you remove the unnecessary backslash by hand in the debugger? — Mong Zhu, Feb 19 '18 at 10:51
@MongZhu the Orders folder exist. and if i remove the unnecessary backslashes nothing changed — M. rit, Feb 19 '18 at 10:56
@SteveHarris I've learned my lesson. thank you for showing me this and improved me :) — M. rit, Feb 19 '18 at 10:56
by the way, if you are in control of the database structure, consider using an appropriate type (Date, DateTime) instead of sting for `s_date` , `e_date` and other columns storing dates — Gian Paolo, Feb 19 '18 at 11:10
and using parameters, among other advantages, will let you easlily handle a client name such "O'Connor" (no need to escape ' if you use parameters) — Gian Paolo, Feb 19 '18 at 11:12

score 2 · Accepted Answer · edited Feb 19 '18 at 11:15

2

It looks like your string ')' is seen as a separate string:

1001gssgsgsgsgs.txt')'

That being said, as has been said in the comments, you must use SqlParameters. Both to prevent Sql Injection and to delimit your strings. Something like this:

SqlCommand command = new SqlCommand('insert into ... values (..., @sub_order)', connection);
command.Parameters.Add("@sub_order", yourSubOrderString);

edited Feb 19 '18 at 11:15

Manfred Radlwimmer

13,257
13
53
62

answered Feb 19 '18 at 11:00

Carra

17,808
7
62
75

can you please explain me how i write the insert into query in this method? `'insert into MyTable (... table fields) values (...,@sub_orders)'` or `'insert into My table values (...,@sub_orders)'` – M. rit Feb 19 '18 at 11:32
1

the table fields are optional but it's good practice to include them, that way if the order of your db fields changes for some reason it still works. – Carra Feb 19 '18 at 12:09

Insert into statement with file path

1 Answers1