Fixing ODBC Filter Strings

SQLFix prepares an string for insertion into an ODBC filter string's WHERE clause. It checks a string for occurrences of the single-quote character('), and replaces each occurrence with two single quotes (''). This allows a string with a single quote to be used in WHERE clause, which is necessary because WHERE operands are delineated by the single quote character, making escapement of embedded single quotes necessary.

The first parameter is a NULL terminated string that contains the text string that will be reformatted. Also, if parameter bIsLike is TRUE, then all occurrences of the underscore & percent characters are surrounded by square brackets (a_b.ps becomes a[_]b.ps). This causes ODBC to view these characters as non-wildcards.

Following is a short example of how SQLFix might be used in your code:


BOOL GetARecord()
{
 CString strName(_T("Fred's Bank"));
 CString strPath(_T("a_b.ps"));
 CMyRecordset myset;

 myset.m_strFilter.Format(_T("[Name] = '%s' and [Path] Like '%%\\%s'"),
 SQLFix(strName), SQLFix(strPath, TRUE));
 if (myset.Open())
 ...
}

In the above example, myset.m_strFilter would be "[Name] = 'Fred''s Bank' and [Path] Like '%%\\a[_]b.ps"
And here's the function:


CString SQLFix(LPCTSTR szText, BOOL bIsLike = FALSE)
// escapes illegal ODBC characters '%_
{
 // quickly determine if we have illegal characters
 if (_tcschr(szText,_T('\'')) == NULL
  && (!bIsLike || _tcspbrk(szText,_T("%_")) == NULL))
  return CString(szText);

 // Searches for "'" and replaces it with "''"
 CString strTemp("");
 int nLength = _tcslen(szText);
 bool bDoLike;

 for ( register int count = 0 ; count < nLength ; count++ )
 {
  // % and _ must be surrounded by square brackets in a LIKE statement
  bDoLike = (bIsLike && (szText[ count ] == _T('_') || szText[ count ] == _T('%')));
  if (bDoLike)
   strTemp += _T('[');

  // add the source character
  strTemp += szText[ count ];

  // single quotes must be escaped with a second single quote
  if( szText[ count ] == _T('\'') )
   strTemp += _T('\'');

  if (bDoLike)
   strTemp += _T(']');
 }
 return strTemp;
}

Date Last Updated: May 17, 1999


Comments

  • How can i use m_strFilter with date?

    Posted by Legacy on 04/11/2003 12:00am

    Originally posted by: dkjf

    How can i use m_strFilter with date?

    Reply
  • how can i conect to the Sqlserver2000 with odbc?

    Posted by Legacy on 12/11/2002 12:00am

    Originally posted by: andychang

    how can i conect to the Sqlserver2000 with odbc?
    

    Reply
  • Wrong way!

    Posted by Legacy on 01/25/2000 12:00am

    Originally posted by: Daniel Cyr

    Great code, but it think your are getting-it wrong.
    
    

    There a way to use parameter with ODBC and DAO that will put de data the way the database will accept-it without having the trouble to know the control caracters.

    There's a lot of kind of database out there and a few of them won't work with your implementation(Double quote is not universal)..(I know most of them do work)..

    ex:
    if you use
    CString m_param2;
    long m_param1;

    m_param1=233;
    m_param2="something";

    m_strFilter="[longfieldName]= ? and [textfieldname] = ?";


    the odbc are dao layer will replace with the proper format
    depending on the variable type.(if the text string contain whatever invalide caracter (control) the engine will fix-it the right way depending on the database type provide by the driver).

    [longfieldName]= 233 and [textfieldname] = 'something'.
    (the engine as replace the first ? by the m_param1 and the next one by "something", You must register parameters first)

    Exemple on request.

    Hope it will help

    Reply
  • A doubt and a little addendum ...

    Posted by Legacy on 05/26/1999 12:00am

    Originally posted by: Homero de la Garza

    First of all, thanks Dean your function is neat and elegant.
    
    

    My question is about the return type of your function which is a local CString object... doesn't it gets destroyed after leaving the function?

    If it is safe to do this sort of things this way that could save me a lot of thinking and typing. Please answer to this when you can.

    The addendum I proposse is the handling of the '[' character that if I remember well, has similar treatement problems. The modification could be something like this:

    //--------------------------------------------------- start
    CString SQLFix(LPCTSTR szText, BOOL bIsLike = FALSE)
    // escapes illegal ODBC characters '%_
    // and [ --- added by HAGL990526
    {
    // quickly determine if we have illegal characters
    if (_tcschr(szText,_T('\'')) == NULL && (!bIsLike || _tcspbrk(szText,_T("%_[")) == NULL))
    return CString(szText);

    // Searches for "'" and replaces it with "''"
    CString strTemp("");
    int nLength = _tcslen(szText);
    bool bDoLike;

    for ( register int count = 0 ; count < nLength ; count++ )
    {
    // % and _ ( and [ ) must be surrounded by square
    //brackets in a LIKE statement
    bDoLike = (bIsLike &&
    (szText[ count ] == _T('_') ||
    szText[ count ] == _T('%') ||
    szText[ count ] == _T('['))); // added code!

    if (bDoLike)
    strTemp += _T('[');

    // add the source character
    strTemp += szText[ count ];

    // single quotes must be escaped with a second
    // single quote
    if( szText[ count ] == _T('\'') )
    strTemp += _T('\'');

    if (bDoLike)
    strTemp += _T(']');

    }
    return strTemp;
    }
    //--------------------------------------------------- end

    Reply
  • Same problem, with DAO

    Posted by Legacy on 05/19/1999 12:00am

    Originally posted by: Daniel MOSMONDOR - Mosh

    I had a similar problem when using DAO, but mainly it was usage of " and ' characters that made problems. I see that you manualy change _ to [_] but you can use CString::Replace function instead.

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • IBM Worklight is a mobile application development platform that lets you extend your business to mobile devices. It is designed to provide an open, comprehensive platform to build, run and manage HTML5, hybrid and native mobile apps.

  • Live Event Date: November 20, 2014 @ 2:00 p.m. ET / 11:00 a.m. PT Are you wanting to target two or more platforms such as iOS, Android, and/or Windows? You are not alone. 90% of enterprises today are targeting two or more platforms. Attend this eSeminar to discover how mobile app developers can rely on one IDE to create applications across platforms and approaches (web, native, and/or hybrid), saving time, money, and effort and introducing apps to market faster. You'll learn the trade-offs for gaining long …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds