For what it's worth, in my class I manage string substitutes -like we have discussed here so far- as well as actual parameters. What we are really doing here is substituting (most likely using StrTran) we are not really sending parameters to the query. SQL engines do manage parameters, as in real parameters. These parameters may be of any type, including binary and nulls. With actual parameters it is possible to send NULLs, Binary data (like jpgs), dates, etc...
To send NULLs and binary data or to avoid SQL injections or when executing the same SQL in a loop, it is best to execute what is known as a "parameterized query" -as in real parameters sent to the sql and not just string substitutions. Such query must first be prepared. Then just before execution the parameters are loaded. This shields the query from injections and when executing in a loop it saves time as the sql engine does not need to parse it over and over each time inside the loop. As you probably already know, most SQL injections are aimed at SQLs with substitutions as they just won't work against a parameterized query.
This is my parameterized query implementation for ADS:
- Code: Select all Expand view
...
bSave := { || ( !EMPTY( oEncTbl:VarPathNo ) .AND. ;
( oQ := TAdsQuery():New(),;
oQ:cSql := ;
"MERGE pathlgs ON pathno = :cPathNo \n"+;
"WHEN NOT MATCHED THEN INSERT ( PathNo, r_date, recno, refer_id, s_date, [User] ) \n"+;
" VALUES ( :cPathNo, :dSrv, :cRec, :cDoc, :dDis, LEFT( User(), 5 ) ); \n"+;
"WHEN MATCHED THEN UPDATE SET PathNo = :cPathNo, \n" +;
" r_date = :dSrv, recno = :cRec, \n"+;
" refer_id = :cDoc, \n"+;
" s_date = :dDis, [user] = LEFT( User(), 5 ) ",;
;
oQ:AdsPrepareSql(),;
;
oQ:SetParameters( { { "cPathNo", oEncTbl:VarPathNo },;
{ "cRec", oPatTbl:VarRecNo },;
{ "cDoc", oEncTbl:VarRefer_id },;
{ "dSrv", oEncTbl:VarR_date },;
{ "dDis", oEncTbl:VarR_date } } ),;
oQ:RunAdsPreparedSql(),;
;
oQ:nLastErr == 0 ) ) .OR. ;
( MsgStop( "Process aborted. Pathology number can not be blank." ), .F. ),;
oQ:End() }
...
//------------------------------------------------------------------
METHOD AdsPrepareSQL( cSql ) CLASS TADSQuery
LOCAL isGood := .F.
DEFAULT ::cAlias := ValidAlias( "SqlArea" )
DEFAULT cSql := ::cSql
cSql := prepareSQLScript( cSql, ::aSubstitutes, ::lDebug ) //takes care of $n$ substitutes.
if Select( ::cAlias ) > 0 ; ( ::cAlias )-> ( DBCLOSEAREA() ) ;endif
::nLastErr := 0 ;::cLastErr := ""
if !empty( cSql )
AdsCacheOpenCursors( 0 )
DBSELECTAREA(0)
IF ADSCreateSQLStatement( ::cAlias, ::nTblType, ::hAdsConnection ) //.or. !ADSVerifySQL( cScript )
isGood := AdsPrepareSQL( cSql )
ENDIF
ENDIF
::LogLastError( isGood, "AdsPrepareSQL", cSql )
::hStatement := AdsGetSQLHandle()
RETURN isGood
//------------------------------------------------------------------
METHOD RunAdsPreparedSql() CLASS TADSQuery
LOCAL oMeter, xRet, isGood := .F.
IF ::lShowProgress
IF ::bProgress == Nil
oMeter := PROGRESSBAR():New( 100, ::cProgressMsg )
ACTIVATE DIALOG oMeter:oDlg NOWAIT ON INIT oMeter:oDlg:center( WndMain() )
::bProgress := { |n| oMeter:Update( n ), oMeter:isCancel }
ENDIF
Register_CallBack( { | nPercent | EVAL( ::bProgress, nPercent ) } )
//AdsRegCallBack( { | nPercent | EVAL( ::bProgress, nPercent ) } )
isGood := AdsExecuteSQL( ::hStatement )
//AdsClrCallBack()
Unregister_callback()
IF oMeter != Nil
::bProgress := NIL
::lWasCanceled := oMeter:isCancel
oMeter:end()
ENDIF
ELSE
isGood := AdsExecuteSQL( ::hStatement )
ENDIF
::LogLastError( isGood, "RunAdsPreparedSql" )
RETURN isGood
//----------------------------------------------------------------------------
//Receives a double dimenssioned array.
//each row contains an array of three values
// 1. fieldname to Set as on parameter name on the parametized query
// 2. value to store on fieldname
// 3. type of value I = Integer, C=Character, D=Date, B=binary, N = double
//
// for some basic xbase data types, the 3rd parameter may be omitted (C,N,D,L)
//
METHOD SetParameters( aParms ) CLASS TADSQuery
LOCAL aParm, cVar, xVal, cType
FOR EACH aParm IN aParms
cVar := aParm[ 1 ]
xVal := aParm[ 2 ]
cType:= IIF( LEN( aParm ) > 2, UPPER( aParm[ 3 ] ), UPPER( VALTYPE( xVal ) ) )
DO CASE
CASE cType == "C" //character data
AdsSetString( cVar, xVal, ::hStatement )
CASE cType == "D" //date
AdsSetDate( cVar, Date2Sql( xVal ), ::hStatement )
CASE cType == "I" //Integer
AdsSetLong( cVar, xVal, ::hStatement )
CASE cType == "B" //binary
AdsSetBinary( cVar, xVal, ::hStatement )
CASE cType == "L" //Logical
AdsSetLogical( cVar, xVal, ::hStatement )
CASE cType == "N" //double
AdsSetDouble( cVar, xVal, ::hStatement )
CASE cType == "U" //NIL?
AdsSetNull( cVar )
END
NEXT
RETURN NIL
Reinaldo.
