DB Escape String

[FuZion]

Hello,

I'm looking for some sort of equivalent to PHP mysql_real_escape_string(). Basically it takes user input and makes it safe to use in queries ( ie preventing injection attacks ). Is there anything of the sort in VB.NET? What would you suggest?

thanks!

 

[ScottyB]

The best way to prevent sql injection attacks is to make use of parameterized stored procedures and also in you connection string using a a least privileged account with restricted permissions on the database. ie only those that your application required to perform its function.

I have included a link that explains these principles a little clearer.

http://msdn.microsoft.com/en-us/library/ms998271.aspx

Hope this helps

Regards

ScottyB

 

[FuZion]

Ok, so how exactly does parameters prevent this?

 

[JohnH]

When using parameters of a command (you don't need SP) the values are not treated as part of the executing SQL command, this makes it impossible to inject code through given values to extend the default command maliciously.

 

[FuZion]

Great, thanks guys!

 

[ScottyB]

Parameters can prevent sql injection because they allow you to check them form type and length.

Wether you use stored procedures or not is a matter of preference. The important thing is to used parameters

Hope this helps

Regards

ScottyB

 

[JohnH]

Parameters can prevent sql injection because they allow you to check them form type and length.

The type check is not the mechanism that prevent injections, but it has the added benefit of data type validation before the command is passed to db engine and executed. This is also used when strongly typed datasets are generated to provide a typed interface, here you get data type error checking at the early stage of design time.

The injection protection is a matter of provider/driver making different calls to db engine. For a query-with-values string db is simply told to execute this string. For query-with-parameters a parameterized prepare command is first sent, db compile it and return a command handle (or return handle of existing command if it was issued before), then the values are passed and db engine is told to execute them with given command handle. This ensures your original prepared sql statement can't be changed by inserting additional commands through the values.

 

[FuZion]

Ok, I really appreciate the responses guys! I have implemented this, but I'm having a problem with the SQL now. When I call cmd.Parameters.Add("?val", paramVal) where paramVal is an integer, the value is added to the SQL with quotes, and since it is an INT datatype there shouldn't be quotes surrounding it, is there a way to fix this?

Thanks!

 

[JohnH]

SqlParameterCollection.Add(String, Object) Method is obsolete. Use one of the other Add methods in combination with setting the Value property or use AddWithValue method.

 

[cjard]

Ok, I really appreciate the responses guys! I have implemented this, but I'm having a problem with the SQL now. When I call cmd.Parameters.Add("?val", paramVal) where paramVal is an integer, the value is added to the SQL with quotes, and since it is an INT datatype there shouldn't be quotes surrounding it, is there a way to fix this?

Thanks!

Are you using access? I cant understand why you'd but a question mark in your parameter name - SQLServer formats its parameters like @parameterName, oracle like arameterName and access simply like ? (you can name parameters anything you like in access, for your own reference pruposes, but access doesnt make use of named parameters in the actual sql so the order of addition is critical)