Login | Register

Fixing ODBC Filter Strings

Posted by Dean Grimm on May 17th, 1999
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

IT Offers

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.

Go Deeper

Most Popular Programming Stories

  • There have been no articles posted today.

More for Developers

Latest Developer Headlines

RSS Feeds