I'm using .Net 4 and Sql Server 2008 and I'm having a bit of trouble with the following parameterised query- I keep getting the error 'Incorrect syntax near '@colName''
I've tried passing the strings in directly and that works, but I know that it isn't the safest thing to do but I cannot figure this out!
I realised that i do n't need to worry about the table/ column names as they can only be set in the code- no user is going to have access to it whatsoever. So, although it's not idea, I can insert them directly as 'tabName' and 'colName'
As for the value field, I tried this and it inserted '@value' into the database, so there's still something I'm doing wrong!
The only thing wrong here is possibility of sql injection, if you pass in Table name or column name like this using string.format() then it may lead to sql injection.
So better avoid this, i mean dont let users enter these values(I mean tabname and colname shouild not be editable from user).
The only thing wrong here is possibility of sql injection, if you pass in Table name or column name like this using string.format() then it may lead to sql injection.
So better avoid this, i mean dont let users enter these values(I mean tabname and colname shouild not be editable from user).
Rest is fine.
The table name and column name aren't set by the user- it's set by an enum I've created and reference throughout.
donpisci
Member
74 Points
178 Posts
Parameterised Queries
May 24, 2012 11:38 AM|LINK
Hi Guys,
I'm using .Net 4 and Sql Server 2008 and I'm having a bit of trouble with the following parameterised query- I keep getting the error 'Incorrect syntax near '@colName''
I've tried passing the strings in directly and that works, but I know that it isn't the safest thing to do but I cannot figure this out!
Here's the code:
string cmd = string.Format("INSERT INTO {0} ({1}) VALUES ('{2}')", "@tabName", "@colName", "@value"); SqlCommand sqlCmd = new SqlCommand(cmd); sqlCmd.Parameters.Add(new SqlParameter("tabName", tabName)); sqlCmd.Parameters.Add(new SqlParameter("colName", colName)); sqlCmd.Parameters.Add(new SqlParameter("value", newItem));Mudasir.Khan
All-Star
15346 Points
3142 Posts
Re: Parameterised Queries
May 24, 2012 11:49 AM|LINK
try this
string cmd = string.Format("INSERT INTO {0} ({1}) VALUES ('{2}')", tabName, colName, "@value"); SqlCommand sqlCmd = new SqlCommand(cmd); sqlCmd.Parameters.Add(new SqlParameter("value", newItem));bbcompent1
All-Star
33718 Points
8737 Posts
Moderator
Re: Parameterised Queries
May 24, 2012 11:50 AM|LINK
Ok, when you call out like that, make sure your parameters look like this - they must exactly match if you reference them:
string cmd = string.Format("INSERT INTO {0} ({1}) VALUES ('{2}')",
"@tabName", "@colName", "@value");
SqlCommand sqlCmd = new SqlCommand(cmd);
sqlCmd.Parameters.Add(new SqlParameter("@tabName", tabName));
sqlCmd.Parameters.Add(new SqlParameter("@colName", colName));
sqlCmd.Parameters.Add(new SqlParameter("@value", newItem));
nijhawan.sau...
All-Star
16460 Points
3178 Posts
Re: Parameterised Queries
May 24, 2012 11:50 AM|LINK
You cannot use parameters with column names as far as i know.
You have to use string.format for passing in table name, column name etc and then for values alone you can use parameters.
The Drawback being, it opens your system to sql injection.
donpisci
Member
74 Points
178 Posts
Re: Parameterised Queries
May 24, 2012 12:13 PM|LINK
Hi All,
Thanks for getting back to me.
I realised that i do n't need to worry about the table/ column names as they can only be set in the code- no user is going to have access to it whatsoever. So, although it's not idea, I can insert them directly as 'tabName' and 'colName'
As for the value field, I tried this and it inserted '@value' into the database, so there's still something I'm doing wrong!
string cmd = string.Format("INSERT INTO {0} ({1}) VALUES ('{2}')", tabName, colName, "@value"); SqlCommand sqlCmd = new SqlCommand(cmd); //sqlCmd.Parameters.Add(new SqlParameter("@tabName", tabName)); //sqlCmd.Parameters.Add(new SqlParameter("colName", colName)); sqlCmd.Parameters.Add(new SqlParameter("@value", newItem));nijhawan.sau...
All-Star
16460 Points
3178 Posts
Re: Parameterised Queries
May 24, 2012 12:34 PM|LINK
The only thing wrong here is possibility of sql injection, if you pass in Table name or column name like this using string.format() then it may lead to sql injection.
So better avoid this, i mean dont let users enter these values(I mean tabname and colname shouild not be editable from user).
Rest is fine.
donpisci
Member
74 Points
178 Posts
Re: Parameterised Queries
May 24, 2012 01:03 PM|LINK
The table name and column name aren't set by the user- it's set by an enum I've created and reference throughout.
bbcompent1
All-Star
33718 Points
8737 Posts
Moderator
Re: Parameterised Queries
May 24, 2012 01:04 PM|LINK
I would just hard-code the table and column name. That wouldn't be injectible.
donpisci
Member
74 Points
178 Posts
Re: Parameterised Queries
May 24, 2012 01:17 PM|LINK
Yes, that's what I've done. as for the value field, it still giving me grief!
bbcompent1
All-Star
33718 Points
8737 Posts
Moderator
Re: Parameterised Queries
May 24, 2012 01:20 PM|LINK
Because you need to do it like this:
sqlCmd.Parameters.Add("@value", System.Data.SqlDbType.VarChar);
SqlCmd.Parameters["@value"].Value = newItem;