Using vfp functions within sqlexec

[insania]

I am trying to use the modulus of a number to insert a value into a postgres database table but cant seem to get the code just right.

At the moment ive got:
lnResult = SQLEXEC(lnConn, "INSERT INTO table1 (tb1_field1, tb1_field2, tb1_field3, tb1_field4, tb1_field5, tb1_field6, tb1_field7) VALUES('abc', ?z_loop, ?(mod(z_loop,2)), CURRENT_DATE, CURRENT_TIME, 'abc', 'abc')")

Ive got ?z_loop insert a numerical value in tb1_field2 but i cant get the modulus to work in order to insert either 1 or 0 as boolean true or false for tb1_field3.

Any help would be appreciated.

 

[Olaf Doschke]

With SQLExec, the executant is the database you access, so you can't use VFP functions when you access a postgres database. Prepare a variable z_odd as z_odd = z_loop%2 beforehand and send ?z_odd to postgres.

Bye, Olaf.

 

[Mike Lewis]

Insania,

You need to do something like this:

lcCode = ;
  "INSERT INTO table1 (tb1_field1, tb1_field2, tb1_field3," + ; 
"tb1_field4, tb1_field5, tb1_field6, tb1_field7) VALUES('abc'," + ;
z_loop + ", " + mod(z_loop,2) + ;
", CURRENT_DATE, CURRENT_TIME, 'abc', 'abc')"
lnResult = SQLEXEC(lnConn, lcCode)

In other words, you build your SQL first, stuffing any required variables or function results into the string, then send the string so formed.

By the way, what are CURRENT_DATE and CURRENT_TIME? If those are VFP variables, the code won't work as it stands. You will have to use the same technique as above for stuffing them into the SQL command. Similarly if they are VFP constants. If they are Postgresql constants or variables, it should be OK. The important thing to remember is that your SQL command will be executed by the back end, and it must be syntactically correct for the back end in question.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[insania]

Thanks to both of you, I ended up using the " + <variable> + " method along with converting it to a string with the vfp variables which are strings and ?<variable> with the vfp variables which are numeric. CURRENT_DATE and CURRENT_TIME are postgres constants so they were fine, postgres really just didnt like sending the boolean 0 and 1 as integers.

Thanks again for the help

 

[Mike Lewis]

Mike,

No need for clarification. I understand exactly what you are saying. Your post raises several issues.

First, I think you are saying that there is fundamental difference in the way that SQL Server interprets a command this is built with a parameter (a variable name preceded by ?) rather than one where the value is supplied literally.

I'm slightly surprised about that, as I thought it was VFP that made the substitution. However, I will take your word for it.

Having accepted that, does that point apply only to SQL Server? You specifically mentioned SQL Server's sp_executesql procedure. Do all back ends have an equivalent function, or does VFP do something different when sending this type of command to SQL Server? (Remember, Insania's back end is Postgres.)

Also, you are assuming that the substituted value (z_loop in my code) is a value entered by the user. There was no indication of that in Insania's post, although it would be sensible to accept it as a possibility. I think my attitude would be that any values entered by the user should in any case be validated before being sent to the server, but I guess that wouldn't always catch the sort of attack you are talking about. (If the value is an address, there would be no way for a validation routine to distinguish between a real address and a function call.)

I guess this is something that we should keep in mind, although I'm not sure if I am going to change my coding practices because of it. I think I would need a little more evidence that doing it the way I suggested was dangerous whereas using parameter substitution removes the danger. Can you suggest any code I can run to test it further?

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Mike Lewis]

Mike,

Thanks for that. I'll try your code when I can grab a moment.

I'm going to talk to Tamar Granor about writing this up as an FPA article.

Maybe you should talk to Christof. This seems to be right up his street.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk