From a security standpoint VFP is a nightmare. You can do pretty much anything on the program end with passwords etc but I never found a way around the fact that you pretty much have to give the client access to the Data directory. VFP being a flat file system (even with the introduction of DBC’s – databases) you had to give the client at the very least read access to the tables and if you wanted him/her to change something then write access too. Only way around that might have been to use VFPOLEDB and a web server but then why use VFP.

Now moving to MYSQL I want to point out some instant benefits you get migrating to MYSQL and that is security. One thing probably not immediatly visible to a newbie is the power of the “DEFINER” and INVOKER security options on stored procedures. This allows clients to do things under certain circumstances that they do have no right to. I want to use the following example to demonstrate the very …

I had someone point out to me that some of the sample code I have used might be vulnerable to an insertion attack. So I feel it is time to address this subject.

First when using examples I try to just focus on the subject of the current blog and often strip a lot of things that are irrelevant to the point I am trying to make out. But now to the subject of keeping your code save.

If you write and SQL statement looking something like

lc_sql = “SELECT * from customer WHERE custno = ‘” + lc_custno + “‘”

Someone could enter “‘; TRUNCATE TABLE customer;’” resulting in  a SQL statement looking like

SELECT * from customer WHERE custno = ”; TRUNCATE TABLE customer; ”

Which could be quite disasterous. So to avoid this we could use parameterized queries which I cannot as I am using a seperate function to wrap around the SQLEXEC() function to do all kinds of things like …

When modifying VFP Forms to run the MYSQL Backend the first question most likely will be: How do I get the data from MYSQL efficiently to my VFP Form without rewriting the whole thing from scratch. The form previously accessing VFP tables most likely opened them in the Data Environment so it might be tempting to just replace them with remote cursors or with cursor adapters mimicking the VFP tables and views previously used. Granted the Form might just work with minimal effort but in most cases this approach would be very inefficient. At the same token the VFP controls on the form most likely are bound to some cursor/alias by use of the recordsource or controlsource properties. So unless one wants to do a lot of coding establishing those links later, some fitting cursor needs to exist before the controls are initialized.

Lets assume a simple scenario where we have a customer screen, with a field where you enter the customer …

So now that we have established some decent contact between VFP and MYSQL it is time to transfer as much as possible from the VFP Database into the MYSQL Schema. Being a long time believer that making a plan can keep you out of trouble I laid out the following steps for that task

• Transfer Table Structures
• Set up Indexes
• Set up foreign keys

So lets look at each step more closely

Transfer Table Structures

Granted we could sit down and take a printout of all the structures and then use Workbench or something like it and start setting up the tables. But being faced with having to do that with close to 100 tables some of which having 40 or so fields this appeared like a daunting task. So I took a look to see how much could be optimized. Tables In MYSQL are created by using the CREATE TABLE ….. syntax which is pretty straight forward. Granted there are some VFP …

So now that the decision was made we had to start to get to work. To show the customer progress and to see what I was up against I downloaded MYSQL Community and set up 3 Servers.

• One on my Vista Machine, to be accessed by the customer to see the progress. So any part finished and tested will be pushed to that server.
• A 2nd one on a WinXP machine in anothter location just to see on how the performance is over the net. After all we don’t want to find out it takes enough time to have a cup of coffe while waiting for the computer to perform a task
• And A 3rd on my Windows 7 laptop.
• And another one is planned on a Linux box at a later point.

So some of you might ask why 3 different Windows platforms? The simple answere is – That is what is mostly out there. This is to replace VFP systems run by small to medium sized companies. They have a budget. Mostly if …

Visual Foxpro was a platform of choice for small and medium applications for decades. I started writing things in Foxpro when it did not belong to Microsoft yet. I believe when it was in version 2.0. In a time when there was no Windows and machines had less than 1 Mb of Ram and a 80 MB harddisk was big. Back then the concern was to keep program and data small. After all those 80 Mb on your Novell Server had to last for a few years. I had a customer back then using SBT and entering about 300 invoices/day with about 15 lines per invoice on average and back then we used about 15 MB a year to store that information…..

Fast Forward to 2010. Said customer still does those 300 invoices on average a day, SBT is long gone, but the software has grown, been updated, improved, Moved to VFP9.0. Many features added, much more information stored. Now those 300 invoices/day run up close to 2 Gb of hard disk space a year. Not a big deal with the size and …