SQLeXEC Assistance 3

[KarenJB]

Hello again VFP gurus...
I have the following dilemma;

when I pass a value of 209475 to this code SQLExec statement number one results in a blank table,
SQLExec statement number two works fine.

What am I doing wrong?

PARAMETERS nID
LOCAL lnHandle
lnHandle = SQLConnect("CE", "stevemm", "suvford")
IF lnHandle > -1

SQLExec(lnHandle,"SELECT * FROM NOTEIINFO WHERE NOTEINFO.ID = nID","RESULTS")

SQLExec(lnHandle,"SELECT * FROM NOTEINFO WHERE NOTEINFO.ID = 209475","RESULTS")

SELECT * FROM RESULTS INTO TABLE NOTEInfo
USE

SQLDisconnect(lnHandle)

ENDIF

 

[JRB-Bldr]

If you were going to use your numeric variable nID you would need to
SQLExec(lnHandle,"SELECT * FROM NOTEIINFO WHERE NOTEINFO.ID = " + ALLTRIMSTR(nID)),"RESULTS")

On a more general note you should examine the SQLExec result value to see if the command executed correctly in the first place.

[B]nRet = SQLExec(lnHandle,"SELECT * FROM NOTEINFO WHERE NOTEINFO.ID = 209475","RESULTS")[/B]

IF nRet = 1
   SELECT * FROM Results
   <whatever>
ENDIF

<whatever else>

Should you find that the SQLExec did not execute correctly, then you should examine the syntax of your SQL command.

Good Luck,
JRB-Bldr

 

[bborissov]

1. When you want to pass a variable/Field value to SQLEXEC() your ALWAYS should use [?] before it.
That marks it as a parameter to SPT:

PARAMETERS nID
LOCAL lnHandle
lnHandle = SQLConnect("CE", "stevemm", "suvford")
IF lnHandle > -1
   SQLExec(lnHandle,"SELECT * FROM NOTEIINFO WHERE NOTEINFO.ID = ?m.nID","RESULTS")
....   
   SQLDisconnect(lnHandle)
ENDIF

2. YOU SHOULD ALWAYS (I cant write this in bigger letters) check the result and check WHAT error ODBC returns:

PARAMETERS nID
LOCAL lnHandle
lnHandle = SQLConnect("CE", "stevemm", "suvford")
IF lnHandle > -1
   
   SQLExec(lnHandle,"SELECT * FROM NOTEIINFO WHERE NOTEINFO.ID = ?m.nID","RESULTS")

   SELECT * FROM RESULTS INTO TABLE NOTEInfo
   USE
   
   SQLDisconnect(lnHandle)
ELSE
   [COLOR=red][b]
   AERROR(laError)
   MessageBox(laError[1,2])
   [/b][/color]
ENDIF

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Mike Lewis]

Karen, both JRB-Bldr's and Borislav's solutions would work.

However, I would quibble at the word "always" in the following:

When you want to pass a variable/Field value to SQLEXEC() your ALWAYS should use [?] before it.

The point is that nID is a local VFP variable. The back-end database knows nothing about it. You have to pass the actual contents of the variable. Whether you do that with the "?" syntax (a la Borslav) or using the method suggested by JRB-Bldr is up to you. They are equally valid.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[bborissov]

Mike, if you pass a some kind of strings and build the whole query before you pass it to SQL Server (or any other RDBMS) you are opened for the SQL injections. When you use parameters you aren't.

lcString = "'; SELECT * FROM CreditCardInfo"
lcSQL = "SELECT * FROM MyTable WHERE SomeField = '"+lcString+"'"
? SQLEXEC(sqlHandler,lcSQL, "crsResult")

[code]
That would bring every thing that you have stored in CreditCardInfo table.
But:
[code]
lcString = "'; SELECT * FROM CreditCardInfo"
lcSQL = "SELECT * FROM MyTable WHERE SomeField =?lcString"
? SQLEXEC(sqlHandler,lcSQL, "crsResult")

is harmless.
Just because two different methods are executed in the backend
1. Executes the query directly
2. uses sp_executesql SP to execute that query.



Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[JRB-Bldr]

Opps!
I see that the typo gremlin has snuck in and struck again ( missing left parenthesis after ALLTRIM )

SQLExec(lnHandle,"SELECT * FROM NOTEIINFO WHERE NOTEINFO.ID = " + ALLTRIM(STR(nID)),"RESULTS")

Good Luck,
JRB-Bldr

 

[KarenJB]

Thanks JRB, Mike, and Borislav - as always I knew I could count on a quick and accurate answer with additional recommendations for being a better programmer. I certainly appreciate all you do. I will be back with more, you can be sure of it.

 

[Mike Lewis]

Borislav,

Why would I allow "'; SELECT * FROM CreditCardInfo" to be stored in lcString (in your example)?

If I did, then of course you're right - it does leave the door open to an injection attack. But since I would have complete control over the contents of the variable, the situation could never arise.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business