Textbox changes .t. string to 1.0 1

[Rutsagig]

I have a textbox to insert email address to mysql database.
Last day, entering an address like john.t.smith@gmail.com, I notice it saved it as john1.0smith@gmail.com.
It understand .t. and .f. as true or false, and change it to 1.0 and 0.0.
How could I solve this?
Thanks

 

[Mike Lewis]

And if you do find something like this:

IF oComDB.TipoDB = 2 
  lcOrden = STRTRAN(lcOrden,".T.","1.0")
  lcOrden = STRTRAN(lcOrden,".F.","0.0")
ENDIF

then it should be enough simply to remove it. In general, it is not necessary to translate VFP logicals (.T. and .F.) to MySQL Tinyint. As noted earlier, this is done automatically behind the scenes.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

You can be pretty sure to find a lot of .T. in code, so I'd search for '1.0', then for "1.0", then for [1.0].

And finally I'd also search for the term SQL, since this is about creating or modifying queries efore they are sent to MySQL. And then SQLEXEC if no remote views are used. I'd also search for MySQL.

Chriss

 

[GriffMG]

All looks like good advice to me.

If the .f./.t. isn't a problem then just removing it is an easy fix.

Once you get a string with an email address in it and you want NOT to rid yourself of it... that's a lot harder.

Gotta say for things like emails, you would probably be better off using a parameter based approach to protect from SQL injection
imagine the damage that could be done by and email address like 'delete from tblUsers where UID <> 0;@wassit.com'

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

And if you don't get anywhere with a search, you might try running under the debugger, with a breakpoint on the variable that holds the relevant string.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

I think his search should be for the definitions of ModRegistro and AddRegistro

If he finds them, the OP could probably replace the .T. and .F. with something unlikely in 'normal text' and treat it like an 'Escape Sequence'
I have laboured the 'escape' part of it here to make the point, it could be more elegant...

lcValores=lcValores+[,]+IIF(.Controls[i].Value,chr(27)+"T"+chr(27),chr(27)+"F"+chr(27)")

and 

lcOrden=lcOrden+IIF(!EMPTY(.Controls[i].Value),chr(27)+"T"+chr(27),chr(27)+"F"+chr(27))+[,]

Then in the ModRegistro and AddRegistro functions (methods?)

IF oComDB.TipoDB = 1 
  lcOrden = strTran(lcOrden,chr(27)+"T"+chr(27),".T.")
  lcOrden = strTran(lcOrden,chr(27)+"F"+chr(27),".F.")
Else
  lcOrden = strTran(lcOrden,chr(27)+"T"+chr(27),"1.0")
  lcOrden = strTran(lcOrden,chr(27)+"F"+chr(27),"0.0")
EndIf

IF oComDB.TipoDB = 1 
  lcValores = strTran(lcValores ,chr(27)+"T"+chr(27),".T.")
  lcValores= strTran(lcValores ,chr(27)+"F"+chr(27),".F.")
Else
  lcValores= strTran(lcValores,chr(27)+"T"+chr(27),"1.0")
  lcValores= strTran(lcValores,chr(27)+"F"+chr(27),"0.0")
EndIf

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Chris Miller]

There would be a simple solution to the VFP vs MySQL dialect by defining constants depending on TipoDB. And then don't do a rplace 'just in time' but use these constants for true or false

#IF DB_IS_VFP
   #DEFINE TRUE .T.
   #DEFINE FALSE .F.
#ELSE
   #DEFINE TRUE 1.0
   #DEFINE FALSE 0.0
#ENDIF

If you should be able to switch databases at runtime (which I don't think) you can make this a normal IF and then perhaps set some properties like goApp.True and goApp.False to the values necessary for the db type.

Solving everything with parameters instead is also possible, but might need more work to adapt to. But SQL injection always has to be considered and parameters are a good first step against it.




Chriss

 

[Rutsagig]

Found it.
There is a procedure in comdb called vfpamsql (vfp to mysql) that claims it changes selects from VFP to mysql.

Here is the code:

*!*	Replacement of where's logical values
DO WHILE ATC(".t.",lcConsulta)>0
	lcConsulta=STUFFC( lcConsulta,ATC(".t.",lcConsulta),3,'1.0')
ENDDO 
DO WHILE ATC(".f.",lcConsulta)>0
	lcConsulta=STUFFC( lcConsulta,ATC(".f.",lcConsulta),3,'0.0')
ENDDO

I think it makes the replacement if finds .t. or .f. in the query but it should do it only if it is in the where clause.
Any advice on how to do it?

 

[GriffMG]

If it's only supposed to do it after the WHERE, you have a problem with the INSERT INTO... because there is no WHERE in there.

I *think* the best approach would be to use the escape sequence I outlined.

lcValores=lcValores+[,]+IIF(.Controls[i].Value,chr(27)+"T"+chr(27),chr(27)+"F"+chr(27)")

and

lcOrden=lcOrden+IIF(!EMPTY(.Controls[i].Value),chr(27)+"T"+chr(27),chr(27)+"F"+chr(27))+[,]

Then in the ModRegistro and AddRegistro functions (methods?)

IF oComDB.TipoDB = 1 
  lcConsulta= strTran(lcConsulta,chr(27)+"T"+chr(27),".T.")
  lcConsulta = strTran(lcConsulta,chr(27)+"F"+chr(27),".F.")
Else
  lcConsulta = strTran(lcConsulta,chr(27)+"T"+chr(27),"1.0")
  lcConsulta = strTran(lcConsulta,chr(27)+"F"+chr(27),"0.0")
EndIf

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

I suggest you also try just removing that entire block of code (or, better, commenting it out) - that is, the block headed "Replacement of where's logical values".

The purpose of this code is to transalte VFP's logical values to MySQL's Tinyint values. As I've said several times before, this step is unnecessary as it is done automatically behind the scenes.

But note Griff's point about this being in a WHERE clause. If that's right, then there might be some other code somewhere else that affects INSERTs.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Slowly, slowly.

What you'd need to keep this mechanism working on queries having actual .t. and .f. would be detecting whether they are within strings or not. This becomes unimportant when you'd switch to parameterization, but that's not a quick change.

My idea for a quick change would be looking for '=.t.' and '=.f.' instead, replacing them with '=1.0' and '=0.0'. That's not solving all possibilities where this still is a wrong replacement, but it'll handle your current problem for sure. You can add looking for '= .t.' and '= .f.' with a space, but I'd assume a query builder routine would not optimize the query for readability but create it as short as possible.

I wouldn't simply comment or remove it. Such code surely was added to previous code adding in '.t.' and '.f.' strings. You could remove this code when you don't put in '.t.' and '.f.' in the first place, but the right true/false syntax for the database used, see my suggestion to use constants for that.

So what's a sensible quick change: I fear Mike isn't right, because when you send over query text VFP does not do any replacements, this would require the .t. and .f. values to be in VFP logical type variables and query parameterization, then VFP would convert .t. and .f. to 1 and 0, but not within query text, that's sent over to ODBC as is. How should your idea of this work, Mike? When you do SQLExec or remote views or CA, you can use the remote database dialect and there can be anything in it VFP doesn't know, so VFP doesn't touch this, why should it? The automatic conversion only happens when you use parameters, nothing is changed in the query text. And the code Rutsagigi found clearly is about variables used for SQL clauses, not single values. Just take into account the changes in lcConsulta are in a while loop, looking for possibly many .t. or .f. occurring. So this isn't a variable passed in as parameter by ?lcConsulta, this is a whole clause.

Or in short, Mike: When you pass over 'Select * From jobs Where active =.t.' VFP does not change that .t. to 1.0. Only when you pass in 'Select * From jobs Where active = ?m.isActive' and have a variable isActive that is .t.. instead of 1.0, VFP will do the conversion to 1.0, becasue the gateway of the ODBC driver will take a logical variable with ODBC logical values and overall this is calle dmarshalling values over through the ODBC standard. But a full query text is neither touched by VFP nor ODBC and arrives at the remote database as you passed it in.

Overall code caring about values within the query text and its clauses is pointing out an SQL injection weakness and a long-term goal should be modifying this to use parameterization.
Chriss

 

[Rutsagig]

Commenting text doesn't work. Ie, it returns this query:
with replacement:

UPDATE usuarios SET cerrojo=1.0 WHERE nuser=2

with replacing code commented:

UPDATE usuarios SET cerrojo=.T. WHERE nuser=2

This last one fails because .T. should be '.T.', and even if I use '.T.', query don't fail but the value is always set to 0.

I was wrong before, this replacement is not should be only for the where clause.
I'll keep trying.

 

[Chris Miller]

Take my advice to ook for =.t. and replace that with =1.0, also look for =.f. and replace with =0.0

Chriss

 

[Mike Lewis]

Chris,

I can now see that you right about automatic conversion from logical to tinyint, and in particular concerning the use of parameters. I know that I have used VFP data types in parameters and had them converted automatically, and that was what I was thinking of. But as you say that doesn't mean it would work if the VFP types are embedded in the SQL code.

So my suggestion to comment-out the relevant code clearly wouldn't work. But at least it was easily reversible.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

The OP needs to comment out the lines he supplied, but replace them with something else, and I think some kind
of 'escape sequence' is going to be his best bet - not necessarily using chr(27), I have seen people use the equivalent
of %%t%%, ||t||, &&t&& or <<t>> as none of them is *likely* within human entered values.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Chris Miller]

Escape sequences are a fine idea, but I don't think the .t. and .f. come from user input for logical fields, a form would use checkboxes or option groups for things and 'translate' that to field = .t., for example.

Places where .t. is added to the query clauses then has to be rewritten, and that could be dozens of places, not just one central function or method.

But I admit the idea to use constants also would need to go through all code and use them instead of .t. and .f. It would be easy to do a search & replace in the whole project, but there are tons of places not about the query builder that should never be changed, as they are not related to MySQL but in simple code like if statements, etc.

I also think you have more to do than fixing an unexpected bug, so I'd note down the need for a change and have a quick fix with only replacing =.t. and =.f., that'll catech the cases used in where clauses and also in setting fields with update-SQL, set field=.t., for example. In inserts you'd replace ,.t. and ,.f. instead.

In tests, you'll find out whether too many .t. and .f. slip through to MySQL and fix those cases individually.

Chriss

 

[GriffMG]

No Chris, the 'real' .t. and .f. don't but the .t. in john.t.smith@gmail.com probably does, and it's really
hard to exclude/replace once it's in a SQL INSERT/UPDATE string.

Clearly if you parameterised it the problem would go away (as is) but that's probably beyond the OP at this point.

Note that the first value in a SQL INSERT would be (.t. or (.f. not ,.t. or ,.f.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Chris Miller]

Griff, it's fine.

Of course, you'd introduce escape sequences so users can have a .t. as part of their mail address, but again, what I consider as unrealistic is to catch all those places actually embedding .t. and .f. by code, not by user input.

If you have form controls which each contribute to query building by adding a 'field=.t.' or 'field=.f.' clause, such code can appear dozens of time in many forms and is much more work to change than trying to think about more specific cases to look for in the final query text, clearly the pure '.t.' and '.f.' are too unspecific, but to introduce a more complex escape sequence means to also need control and knowledge about all places adding such an escape worthy .t. or .f. to the query.


Chriss

 

[GriffMG]

Probably right Chris.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Rutsagig]

I finally change code to make the replace only when .t. is behind = and it seems its working now:

DO WHILE ATC("=.t.",lcConsulta)>0
	lcConsulta=STUFFC( lcConsulta,ATC("=.t.",lcConsulta),4,'=1.0')
ENDDO 
DO WHILE ATC("=.f.",lcConsulta)>0
	lcConsulta=STUFFC( lcConsulta,ATC("=.f.",lcConsulta),4,'=0.0')
ENDDO

Thanks all

 

[GriffMG]

That would seem to be a sensible way to do it, well done.

That sorts the UPDATE, but not the

INSERT INTO myTable (email,logicalfield) values ('J.F.K@DeadPresidents.com',.f.)

will still remain no good for MySQL

it needs to become

INSERT INTO myTable (email,logicalfield) values ('J.F.K@DeadPresidents.com',0.0)

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​