Numeric macro with where clause in selectcmd 2

[Afriza]

Hello,

I need to use a cursor adapter in data environment and i try to get data with my below command and it's not works

lnVar = 100
This.SelectCmd = "Select * From mytable WHERE _id = lnVar
This.CursorFill()

i've try to use
lnVar = 100
This.SelectCmd = "Select * From mytable WHERE _id = '&lnVar'
and
This.SelectCmd = "Select * From mytable WHERE _id = <<lnVar>>

but i've got the same errors, does anyone can help whats the command should i use ??

thanks n regards

 

[Olaf Doschke]

I hope you don't just have forgotten the closing quote.

This.SelectCmd = "Select * From mytable WHERE _id = ?lnVar", as in views.

You have to think of one thing:
This is not direct code executed right away, it's a string, the cursor adapter does not run in your scope of the code, it has no access to your variables, and especially if this is a cursor adapter connecting to SQL Server, this will have even less idea of your other code and environment. Then this query isn't even run by VFP, but whatever database you address. VFP allows forwarding of VFP variable via ?var, as with view, as in VFP source code itself, too. You don't need to have extra code adding variables to a certain parameter collection send with the SQL command. That's even more luxurious than what C#.NET or other languages ask you to do.

Just look at the error messages coming back.

Bye, Olaf.

 

[Mike Lewis]

You can't use lnVar directly, because that's a variable within your VFP program and is therefore not known to the back end. You need something like this:

[tt]This.SelectCmd = "Select * From mytable WHERE _id = " + TRANSFORM(lnVar)[/tt]

That way, you will testing the contents of lnVar rather than "lnVar" as a literal string.

Edit: I just realised that lnVar is numeric, so you will need to wrap it in a TRANSFORM() to convert it to a string (which you then concatenate to your SelectCmd). This is now shown above.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Well, you can use ?lnVar, you just have to ensure lnVar exists when the Cursoradapter is executing the query.

Substituting values into SQL queries is simple enough in such a case, but can introduce serious risks with SQL injection, if users may enter anything. And in case of using TRANSFORM() on any user input already being a string an not a number, Transform() will not error, but simply pass this string user input on as is.

Basing all your cursor adapter on a class you can introduce a mechanism for parameters in the form of the cursor adapter instance properties, by creating oParam = THIS on the fly just before the SelectCmd is executed and using ?oParam.property in your SelectCmd and similar SQL of the CAs, this becomes a very concise and self-contained solution getting no scope problems.

Situations I mean with "On the fly" are the cursor adapter events like BeforeCursorFill(), BeforeCursorRefresh() etc. And thanks to OOP you program that once only and then use it many many times.

Bye, Olaf.

 

[Afriza]

Thanks to all for any helps,

Especially to Mike Lewis your code suggested is work (use Transform(lnVar) and the value still numeric)

Good Day

 

[Olaf Doschke]

>and the value still numeric
Well, no. Transform turns any data type into a string. A string, not a quoted string, just a string. You need a string to add to another string, And you need the string of a number within a SQL command string, of course. So yes this works.

If users would enter this number and could also enter "0; DROP DATABASE Yourdatabase" you would be busted, though, if that is sent to MSSQL or MySQL.

Ig you restrivted users premission, fine. But there are a gazillion other things they can do.

You better read up on sql injection and begin thinking about query parameterization.

Bye, Olaf.

 

[Afriza]

Hi Olaf Doschke,

i've tried your code suggested but the error messages still occurs as "No value given for one or more required parameters"

here is my code
lnVar = 100
This.SelectCmd = "Select * From mytable WHERE _id = ?lnVar"

Yes you are right, the value not the number but still string without quoted ( "lnVar" ).

I have trouble understanding your suggest as

by creating oParam = THIS on the fly just before the SelectCmd is executed and using ?oParam.property

If you don't mind, would you give me any sample code to create and use oParam = This for my case ?

Thanks Olaf

 

[Olaf Doschke]

There is no one-liner solution if you want to incorporate this solution. Read all I wrote, not just the code sections. Your variable needs to be in current scope when the cursor adapter executes the query. It does not suffice if that's the case during the setting of the SelectCmd.

It nevertheless pays to go through all the necessary steps to implement the proposed solution.

Begin by doing something not recommended and make your parameter variables public, that'll prove the concept in the first place:

This.SelectCmd = "Select * From mytable WHERE _id = ?gnVar"
PUBLIC gnVar
gnVar = lnVar

By intention, this is creating the public variable after setting SelectCmd, as that never is the point in time it executes anyway. It's the point in time of execution, where the variable needs to exist for VFP and that'll always be the case with a public variable as simples case. Creating the var after defining its use at least makes clear the setting of SelctCmd is not, where VFP becomes active and adds in the gnVar value.

Just to give a hint on how this already is safer than Transform(lnVar). If a user is capable of entering that same attack again "0; DROP DATABASE Yourdatabase", then this only will cause a type error, as _id is not a char field.

Bye, Olaf.

 

[Afriza]

Thanks Olaf Doschke,

If you don't mind i need any references (articles url, books online, etc)to learn more how to use CAs with the many ways


regards

 

[Olaf Doschke]

The VFP help is a good start as a reference and also teaching usage.
Help topics:

https://msdn.microsoft.com/en-us/library/aa977156(v=vs.71).aspx

https://msdn.microsoft.com/en-us/library/aa976749(v=vs.71).aspx

In general, the help is more than a reference to every command, class, method, event, property, etc, but also has many chapters in the sense of walkthroughs.


Still, many things I told about is more general knowledg4e, variable scope is a root cause for many errors, if you never learn about it, for example, and has nothing to do with cursor adapters alone.

Parameterization also isn't new with cursor adapters, it's taught with views and SQL passthrough and continues to work the same way.

Take a look at Hentzenwerke for books about VFP in the first place. CAs are of course a thing of VFP8+9 and are taught in the "What's new in 8/9" books, for example.

Bye, Olaf.