Escape SQL string in Business One

Question

How do I escape a string coming from the user in BusinessOne? I have seen some examples (in the official samples!) that seem sensible to SQL injection:

SAPbobsCOM.Recordset RecSet = ( ( SAPbobsCOM.Recordset )( oCompany.GetBusinessObject( SAPbobsCOM.BoObjectTypes.BoRecordset ) ) ); 
string QryStr = "update [@VIDS] set U_RENTED = 'Y', U_CARDCODE = '" + CardStr + "' where Code = '" + MovieStr + "'"; 
            RecSet.DoQuery( QryStr );
RecSet.DoQuery( QryStr );

Is there a way to avoid SQL injection with simple SQL queries (without stored procedures)?

Answer 1

Unfortunately the DI-API does not provide prepared statements therefore you need to secure your inputs manually. Research one how this can be done is contained in the How to escape value in MSSQL server question:

After reading New SQL Truncation Attacks And How To Avoid Them it seems sufficient to escape identifiers using [ (and doubling each occurrence of ]) and values using ' (again doubling every occurence of '). A hint to a library whose concern is correct escaping of MSSQL would be nice nevertheless.

I'm using the following Java code to escape identifiers and values (porting to different languages should be trivial):

Quoting identifiers

public static String identifier(final CharSequence identifier) {
    final int length = identifier.length();
    StringBuilder sb = new StringBuilder(2 + length * 2);

    sb.append('[');

    for (int i = 0; i < length; ++i) {
        char c = identifier.charAt(i);

        if (']' == c) {
            sb.append(']');
        }
        sb.append(c);
    }
    sb.append(']');

    return sb.toString();
}

Quoting values

public static String value(final CharSequence value) {
    final int length = value.length();
    StringBuilder sb = new StringBuilder(2 + length * 2);

    sb.append('\'');

    for (int i = 0; i < length; ++i) {
        char c = value.charAt(i);

        if ('\'' == c) {
            sb.append('\'');
        }
        sb.append(c);
    }
    sb.append('\'');

    return sb.toString();
}