SQL parameters

  • Thread starter Thread starter Chris Capel
  • Start date Start date
C

Chris Capel

In manually creating SQL queries, it would be nice to have a Framework
function that makes input strings safe to add in a manner like this:

myDescription = MethodToMakeStringQuerySafe(myDescription);

new SqlCommand(
String.Format("INSERT INTO Descriptions (Description) VALUES '{0}'",
myDescription)
).ExecuteNonQuery(); //yes, I know this isn't very good

Is there any such thing? Or must I be relegated to doing something like

myDescription = myDescription.Replace("'", "\\'").Replace(etc);

Another thing. I would think that escaping the single quotes in the input
string would be all that's necessary to make the string safe. Is this
correct?

Chris
 
managing all the exotic characters into a string is
difficult and ADO can do it for you!

Just créate a parameter instance, déclare its value and
add it to the Parameters collection of the Command object
 
SqlCommand.Parameters.Add() is what you're after. It will handle escaping
and formatting so that the server understands (quoting text, not quoting
numbers, formatting dates). It also stops your users being able to type
things like "hello'GO;DROP DATABASE ..." in a text box and having the
execution of that sql command do very nasty things.

Niall
 
This is very nice, but how exactly would I use a SqlParameter in a
SqlCommand that I don't make using the designer? Specifically, how would I
reference the paremeter from the SQL query string I give to the SqlCommand?

Chris
 
Like this:

SqlCommand Command = new SqlCommand("SELECT * FROM Table WHERE Field =
@FieldValue");
Command.Connection = ConnectionObject;
Command.Parameters.Add("@FieldValue", "HELLO");
<etc etc>

Niall
 
Back
Top