TEXT...ENDTEXT can't I use "THISFORM"? 1

[mdav2]

I've been trying to build my SQL statements using TEXT...ENDTEXT to create simplier to read statements and to protect from SQL injection. I have found that I can't use THISFORM in the text content but can use variables. I have looked up the help and it basically says that the textlines can consist of:

Parameters
TextLines
Specifies text to send to the current output device. TextLines can consist of text, memory variables, array elements, expressions, functions, or any combination of these.

I am assuming that it doesn't consider thisform.xxxx as a variable hence the following code fails:

TEXT TO m.lcsql noshow
	SELECT * 
	FROM client 
	WHERE clientref = <<ALLTRIM(thisform.cclientref)>>
ENDTEXT

but if I store the value into a variable it does work:

cClientRef = <<ALLTRIM(thisform.cclientref)>>
TEXT TO m.lcsql noshow
	SELECT * 
	FROM client 
	WHERE clientref = ?cClientRef
ENDTEXT

I assume then that if I have values in a field in a table then can I just reference them with the fieldname?

I think it would probably be best to set all the variables first in a list outside the TEXT command to ensure the correct value is being picked up. Just seems a waste the microsoft didn't see form properties as something people may want to pass into a TEXT...ENDTEXT command. Be interesting to see what other people do to with regards using TEXT...ENDTEXT to create SQL commands.

Thanks,

Mark.

Mark Davies
Warwickshire County Council

 

[cbasoz]

First version would work with a slight change:

TEXT TO m.lcsql TEXTMERGE noshow 
    SELECT * 
    FROM client 
    WHERE clientref = <<ALLTRIM(thisform.cclientref)>>
ENDTEXT

However that would be an invitation to SQL injection.
You could do these:

TEXT TO m.lcsql noshow 
    SELECT * 
    FROM client 
    WHERE clientref = ?ALLTRIM(thisform.cclientref)
ENDTEXT

TEXT TO m.lcsql noshow 
    SELECT * 
    FROM client 
    WHERE clientref = ?m.cclientref
ENDTEXT

cclientref = ALLTRIM(thisform.cclientref)

TEXT TO m.lcsql noshow 
    SELECT * 
    FROM client 
    WHERE clientref = ?myAlias.cclientref
ENDTEXT

Unlike first version latter 3 are parametric and prevents SQL injection.

Cetin Basoz
MS Foxpro MVP, MCP

 

[cbasoz]

A slight correction (BUT I strongly suggest against using this style (SQL injection invitation).

TEXT TO m.lcsql TEXTMERGE noshow 
    SELECT * 
    FROM client 
    WHERE clientref = '<<ALLTRIM(thisform.cclientref)>>'
ENDTEXT

Cetin Basoz
MS Foxpro MVP, MCP

 

[mdav2]

Thanks for the response cbasoz. As you suggested by the correction this code doesn't work:

TEXT TO m.lcsql noshow
    SELECT *
    FROM client
    WHERE clientref = ?ALLTRIM(thisform.cclientref)
ENDTEXT

It actually prompts me to enter a parameter when run in an executable state which is interesting. The other two code suggestions will be applied where applicable which saves me converting the table values into variables which was what I was thinking of doing.

Thanks,

Mark.

Mark Davies
Warwickshire County Council

 

[cbasoz]

I don't know what you may be doing wrong but it works from an executable. Probably in your case thisform.cclientref do not exist.
I just tried this:

*SQLServerParameterTest.prg

Local loSample
loSample = Createobject('SampleForm')
loSample.Show(1)

Define Class SampleForm As Form
  Height = 400
  Width = 600
  DataSession = 2

  Add Object txtCountry As TextBox With Top=5,Left=5,Width=75
  Add Object btnCustomer As CommandButton With Top = 5, Left = 85, ;
    caption = 'Get Customers', AutoSize = .T.
  Add Object grdCustomers As Grid With Top=40, Height=350, Width=600

  Procedure btnCustomer.Click
    Local lnHandle,lcSQL
    TEXT TO m.lcsql noshow
    SELECT *
    FROM Northwind..Customers
    WHERE country = ?ALLTRIM(thisform.txtCountry.Value)
    ENDTEXT
    lnHandle = Sqlstringconnect('Driver={SQL Native Client};server=.\sqlexpress;Trusted_connection=yes')
    SQLExec(m.lnHandle,m.lcSQL,'MyCustomers')
    SQLDisconnect(0)
    Thisform.grdCustomers.RecordSource = 'MyCustomers'
  Endproc
Enddefine

Compiled to an exe using:

BUILD PROJECT SQLServerParameterTest FROM SQLServerParameterTest.prg
BUILD EXE ('c:\Temp\SQLServerParameterTest.exe') FROM 'SQLServerParameterTest.pjx'

and it works perfectly well.


Cetin Basoz
MS Foxpro MVP, MCP

 

[Mike Lewis]

Mark,

It actually prompts me to enter a parameter when run in an executable state which is interesting.

Just a thought ... Are you running your SQLEXEC() in the same method as the TEXT / ENDTEXT?

If the SQLEXEC() is in a different class or a procedure, then it won't know about THISFORM, which might explain why you see a prompt.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[alan92rttt]

Lets start from your initial code block and work from there

TEXT TO m.lcsql noshow
    SELECT *
    FROM client
    WHERE clientref = <<ALLTRIM(thisform.cclientref)>>
ENDTEXT

their are two issue here:
1) bad syntax
2) the potential for SQL Injection

Lets handle the syntax issue first
this "TEXT TO m.lcsql noshow" is missing the TEXTMERGE clause. It should look like "TEXT TO m.lcsql noshow TEXTMERGE"

This change will let your code run but it may leave you open to potential security issues. This link will give you some basics on the issue.

http://en.wikipedia.org/wiki/SQL_injection

To prevent the issue you would need to totally change your process.

Cetin, provided the code for this.

TEXT TO m.lcsql noshow
    SELECT *
    FROM client
    WHERE clientref = ?m.cclientref
ENDTEXT

m.cclientref = ALLTRIM(thisform.cclientref)

As you can see their is no TEXTMERGE clause in the command and your form property has been saved to a memory variable.

At this point you have a safe SQL and your value in a variable that can be sent as a parameter.

The SQLEXEC() will pass the SQL command and the variable as separate pieces of data(you don't have to do anything extra to make it work). These separate pieces are combined in a secure way on the SQL server.






Alan