Recordset question

[Former Member]

hey All,

Does anyone know how I can ensure my SQL statements that get passed to the recordset object do not contain any illegal characters?

For ex we use the following code:

RS.DoQuery("SELECT U_ZONE FROM OCPR WHERE CardCode = '" + vendCode + "' AND NAME = '" + shipFrom + "' ORDER BY Name");

If the shipFrom variable has single quotes it will throw an SQL exception.

In normal .NET I would use a sqlparameter of course to fix this issue. Has anyone found a similar method in the SDK?

[Former Member]

I haven't seen any features in the SDK itself to help you with this issue. It is however a common problem across a number development environments and can be solved by just writing your own sqlEncode function.

A simple example that would handle your single quote issue could look like

Function sqlEncode(sqlValue) 
     sqlEncode = replace(sqlValue, "'", "''")
End function

Simply place the function around any of your variables used within sql strings.

... CardCode = '" + sqlEncode(vendCode) + "' AND ...

Personally I prefer the option of not allowing these special characters into the data in the first place.

Regards,

John.

[Former Member]

Thanks alot John. That is exactly how I am handling it right now. I do not allow these characters in places where I have control over the input but in other places the users enter this information through the business one interface and i cannot stop that.

I am surprised that Business One does not somehow validate the raw SQL that is passed into the objects.