PlanetMysql.ru — информация о СУБД MySQL

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 usefulness of that feature.

First of course we wont give any of the administrative roles to the user accounts used to access the server. We probably wont even give that account the “DELETE” right on the schema. We do not want an errant DELETE…. to run havoc on our tables – accidental or malicious as it may be. But there might be a scenario where the client needs to delete something.

We can accomplish this by wrapping the DELETE statemente into a stored procedure We created the stored procedure with a statement something like

CREATE DEFINER = `user_with delete_rights`@`%` PROCEDURE `deletesomething` (<some parameters>)
BEGIN
-- some checking of the parameters and
DELETE ...... -- our delete statement probable a prepared one

We do not need to add “SECURITY DEFINER” as that is the default option. The above means that the statements within the procedure are executed as if the user was the user_with delete_rights user and not the regular user account that logged in. This way now our client can delete what he needs to delete and we can make sure that he cannot delete anything else accidentally or otherwise.

He needs the “EXECUTE” priviledge to run the “CALL …. ” statement. But that is all he needs for that. If one is so inclined one could wrap pretty much any statement into a STORED PROCEDURE wrapper and do the same for INSERT, UPDATE and maybe even SELECT statements to enhance the security and integrity of the whole system

PlanetMySQL Voting: Vote UP / Vote DOWN

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 usefulness of that feature.

First of course we wont give any of the administrative roles to the user accounts used to access the server. We probably wont even give that account the “DELETE” right on the schema. We do not want an errant DELETE…. to run havoc on our tables – accidental or malicious as it may be. But there might be a scenario where the client needs to delete something.

We can accomplish this by wrapping the DELETE statemente into a stored procedure We created the stored procedure with a statement something like

CREATE DEFINER = `user_with delete_rights`@`%` PROCEDURE `deletesomething` (<some parameters>)
BEGIN
-- some checking of the parameters and
DELETE ...... -- our delete statement probable a prepared one

We do not need to add “SECURITY DEFINER” as that is the default option. The above means that the statements within the procedure are executed as if the user was the user_with delete_rights user and not the regular user account that logged in. This way now our client can delete what he needs to delete and we can make sure that he cannot delete anything else accidentally or otherwise.

He needs the “EXECUTE” priviledge to run the “CALL …. ” statement. But that is all he needs for that. If one is so inclined one could wrap pretty much any statement into a STORED PROCEDURE wrapper and do the same for INSERT, UPDATE and maybe even SELECT statements to enhance the security and integrity of the whole system

PlanetMySQL Voting: Vote UP / Vote DOWN

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 connection maintenance, Error handling, Logging and even in some cases sending an email to the IT when certain conditions arise. So the above mentioned problem needs to be addressed when creating the sql statement.

The first line of protection is to properly escape any string I send. The table below is copied from the MYSQL manual showing what needs escaping

\0	An ASCII NUL (0x00) character.
\'	A single quote (“'”) character.
\"	A double quote (“"”) character.
\b	A backspace character.
\n	A newline (linefeed) character.
\r	A carriage return character.
\t	A tab character.
\Z	ASCII 26 (Control-Z). See note following the table.
\\	A backslash (“\”) character.
\%	A “%” character. See note following the table.
\_	A “_” character. See note following the table.

So I wrote a vfp function called addslashes()

FUNCTION Addslashes

LPARAMETERS lc_str, ll_notrim

lc_str = STRTRAN(lc_str,”\”, “\\”)

lc_str = STRTRAN(lc_str,”‘”, “\’”)

lc_str = STRTRAN(lc_str,’”‘, ‘\”‘)

lc_str = STRTRAN(lc_str,’%', ‘\%’)

lc_str = STRTRAN(lc_str,’_', ‘\_’)

lc_str = STRTRAN(lc_str,CHR(0), ‘\0′)

lc_str = STRTRAN(lc_str,CHR(10), ‘\n’)

lc_str = STRTRAN(lc_str,CHR(13), ‘\r’)

lc_str = STRTRAN(lc_str,CHR(26), ‘\z’)

lc_str = STRTRAN(lc_str,CHR(9), ‘\t’)

lc_str = STRTRAN(lc_str,CHR(8), ‘\b’)

IF !ll_notrim THEN

      lc_str = RTRIM(lc_Str)

ENDIF

RETURN lc_str

I could Have used a FOR … NEXT Loop with a CASE statement and loop through the string but found that that was slower than calling the STRTRAN() function multiple times. So now we run every string through this routine and the above SQL statement becomes

lc_sql = “SELECT * FROM customer WHERE custno = ‘” + ADDSLASHES(lc_custno) + “‘”

And the above attempt to mess with the system will produce an sql now looking like

SELECT * from customer WHERE custno = ‘\’; TRUNCATE TABLE customer; \”

Which would just return an empty record set.

This will also allow me now to insert and retrieve string like “John’s Barber Shop” or multi line entries. Furthermore I made it a rule to emulate strong typing when it comes to SQL statements. Simply to to prevent a “Garbage in”/”Garbage out” Scenario. So Each textget has its validation routine and is bound to a local cursor. VFP allows you to change the type of a variable by simple assigning some different type of data to it but fields in a cursor are of a fixed type. This also allows us to use functions to build the SQL statements based on the underlying cursor. So every string and Memofield gets treated with ADDSLASHES() each numeric with STR(), Each date field WITH DTOS() to make a MYSQL valid date string etc.

At this point some might say it looks like a lot of work and some of this is replicating what VFP does through remote views. So why use SQL Pass through and not remote views? The single aswer is performance. This way I can control what to send when and how and avoid data being sent unnecessarily or at inoportune times.

PlanetMySQL Voting: Vote UP / Vote DOWN

 

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 code, a little browse control that pops up to give you some choices in case your entry does not exist, and then lots of controls displaying the customer information. The original screen most likely worked by just putting the customer table into the dataenvironment and then bind the controls to it.

So if we replace that with a remote view in the dataenvironment the remote view would have to have “SELECT * FROM customer” as its select statement. If the customer file is very small and not expected to grow much it might be just the easiest to insert a “customer” cursoradaptor into the dataenvironment and use the builder to establish the connection and update method and let VFP take care of the rest. The drawback of this method is that most likely a lot of data would be exchanged between the server and the form that is not really needed. So if the customer table is large and we have a slow connection – maybe over the internet – the performance might be unacceptable. So what can we do to improve performance without getting into too big of a rewrite.

Accessing Data remotly we probably want to keep the traffic between VFP and MYSQL at a minimum. We can accomplish that by using SQL passthrough This gives us full control and the possibility to optimize.

First we are going to have to establish a cursor for the controls before the load. I have found that the forms “LOAD” event is a good place for that. I have a UDF called MYSQLEXEC() that handles all the connection establishing and communication with the MYSQL server. I might write in another blog about it but for now just use it. Its Syntax is MYSQLEXEC(<sqlstatement>, <cursoralias>,<errormessage>, <assync>) It returns .t or .f. based on its success

So in the LOAD event I would put

IF !MYSQLEXEC(“SELECT * FROM customer LIMIT 0”, “customer”, “Cannot access customer File”, .f.)

    THISFORM.RELEASE

    RETURN

ENDIF

This will create a cursor with 0 records – which is enough for the controls to have something to bind to.

Later in the VALID or LOSTFOCUS events of the Customer # textbox I might have had some code like

SELECT customer

IF !SEEK(THIS.value)

• whatever we did to display the browse screen/control

ENDIF

I could now replace this with

IF !MYSQLEXEC(“SELECT * FROM customer WHERE custno =’” + THIS.value + “’”, “c_temp”, “Cannot access customer File”, .f.)

    THISFORM.RELEASE

RETURN

ENDIF

SELECT customer

ZAP && remove previous record from the customer cursor

INSERT INTO customer SELECT * FROM c_temp

You might ask why we do not load the data directly into the customer cursor. The reason is that SQLEXEC which we have to use for passthrough will close the old “customer” cursor and then reestablish a new one which causes grids and other controls to unbind. In grids all columns and controls attached to them would also loose their bindings and we would have to write a routine to establish it again. By ZAPping the customer cursor and then inserting from the c_temp cursor into the customer cursor no unbinding happens and all we have to do is a

THISFORM.REFRESH

So with this method we only transferred the structure an one record over the connection. To further improve on this we could replace the “SELECT *” WITH “SELECT <fieldlist>” to only retrieve the fields we need.

This might especially be benefitial for the Browse screen which we might display in case the entered customer # does not exist. Lets assume that in case the customer selected and incorrect customer code or just entered a portion of it we want to display maybe 20 records close to the customers selection with 10 records before and 10 after it. In VFP we might not have cared and attached the whole “customer” table to the browse screen to allow the user to scroll through the whole database but that again would require to retrieve at the very least a certain number of fields for every record in the table. In foxpro we might have had something like: (assuming lc_custno contains the customer # that was not found)

SELECT customer

SET SOFTSEEK ON

SEEK lc_custno

SKIP -10

IF BOF()

    GO TOP

ENDIF

• display/refresh the grid. (in the lostfocus event of course)

For mysql we first might change the binding from “customer” to something like “seekcust” to not interfere with the “customer” cursor used by the other controls and create a sql string something like this:

lc_sql = “SELECT * FROM (SELECT custno, company from customer where custno < ‘” + ;

lc_custno + “’ ORDER by custno desc limit 10) a “ + ;

“UNION SELECT * FROM( SELECT custno, company from customer where custno >= ‘” + ;

lc_custno + “’ ORDER by custno asc limit 10) a” + ;

“ORDER BY custno ASC”

By Selecting the 10 after and the 10 before the selected value and then sorting them by customer # we just transmit custno and company for 20 records. We then could put a “Next” an “Prev” button onto that little popup control that retrieve the next/prev 20 records. When the customer selects a customer we could stuff that value into the customer # setection textget and execute the “VALID” event

PlanetMySQL Voting: Vote UP / Vote DOWN

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 features that MYSQL does not have and some that are available in MYSQL and lacking in VFP but at least for the overlapping part one could quickly write some VFP code as follows:

FUNCTION CreateTable

PARAMETERS lc_sourcetable, lc_desttable

USE (lc_sourcetable) AGAIN ALIAS a_conv

ln_c = AFIELDS(la_f)

lc_sql = “DROP TABLE IF EXISTS `”+lc_desttable + “`”

IF !MYSQLEXEC(lc_sql, .f., “Cannot drop table”)

    RETURN .f.

ENDIF

lc_sql = “CREATE TABLE `”+lc_desttable + “` (“

FOR ln = 1 TO ln_c

    IF ln > 1

       lc_sql = lc_sql + “, “

    ENDIF

 

    lc_sql = lc_sql + “`” + la_f(ln, 1) + “` ”

 

    DO CASE

    CASE la_f(ln, 2) = “C”

        lc_sql = lc_sql + “VARCHAR(” + ALLTRIM(STR(la_f(ln, 3), 10, 0)) + “)”

    CASE la_f(ln, 2) = “D”

        lc_sql = lc_sql + “DATE”

    CASE la_f(ln, 2) = “T”

        lc_sql = lc_sql + “DATETIME”

     CASE la_f(ln, 2) = “M”

         lc_sql = lc_sql + “TEXT”

     CASE la_f(ln, 2) = “L”

         lc_sql = lc_sql + “BIT”

     CASE la_f(ln, 2) = “N”

         lc_sql = lc_sql + “DECIMAL(” + + ALLTRIM(STR(la_f(ln, 3), 10, 0)) + ;

         ”, “ + ALLTRIM(STR(la_f(ln, 4), 10, 0)) + “)”

     CASE la_f(ln, 2) = “I”

         lc_Sql = lc_sql + “INTEGER”

     CASE la_f(ln, 2) = “G”

         lc_sql = lc_sql + “MEDIUMBLOB”

    ENDCASE

 

    IF !la_f(ln, 5)

        lc_sql = lc_sql + ” NULL”

    ELSE

        lc_sql = lc_sql + ” NOT NULL”

    ENDIF

 

    IF !EMPTY(la_f(ln, 9))

        lc_sql = lc_sql + ” DEFAULT ‘” + la_f(ln, 9) + “‘”

    ENDIF

NEXT

lc_sql = lc_sql + “) ENGINE = INNODB”

IF !MYSQLEXEC(lc_sql, .f., “Cannot create table”)

    RETURN .f.

ENDIF

RETURN .t.

This Function simple creates a CREATE TABLE statement for the VFP table <lc_sourcetable> sent to it an creates a mysql table named <lc_desttable> if it is successful then it returns .t. otherwise .f. The MYSQLEXEC() function is a udf I wrote that handles all the MYSQL traffic including setting up connections and error logging/reporting but could have been accomplished with a combination of SQLCONNECT() and SQLEXEC() functions. We are using the AFIELDS() VFP function to get an array of all the fields in the TABLE and the according properties.

Now all one needs to do is some code like

OPEN DATABASE myvfpdb

ln_tcnt = ADBOBJECTS(la_t, “TABLE”) && get a list of all the tables

FOR x = 1 TO ln_tcnt && loop through all the tables

    WAIT “Creating ” + la_t(x) WINDOW NOWAIT

    IF !createtable(“<pathtodatadir>\” + la_t(x), la_t(x))

        *what ever error handling you want to do

    ENDIF

NEXT

So now that we got the structures across we can get to

Transfer Indexes and Foreign Keys

Unfortunatly in my case I did not come up with a way that was quicker by writing some code than to do this manually. VFP allows too many things in Indexes to drastically reduce the number that could be transferred by a program. Simpe VFP things like using UDF’s or VFP functions in indexes and also FOR expressions (filters) on indexes. A quick look at the documentation showed that less than 20 % of my indexes could be transferred programatically. This also means that quite some code has to be changed so transferring the same Indexes used in VFP is not a good idea. Indexes are there to improve data retrieval so they need to be optimized to the particular engine.

VFP accomplishes referential integrity with triggers. And there again the two platforms are too different to just write a short procedure to accomplish this. And in this case MYSQL with INNODB has some features that VFP does not have. So it made more sense to do this manually and examine each case and set it up in the way most suitable for MYSQL.

Next: transferring the Data

PlanetMySQL Voting: Vote UP / Vote DOWN

 

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 you can bring it in und 10,000 you got the sale. So it benefits if one can use existing equipment.

I am happy to report that all 3 installs went smoothly and w/o any problems.

Next came the task of installing and testing the ODBC driver.

On the Vistat 32 bit and XP machine – no problem at all. On my Windows 7 I initially was perplexed as after the installation of the 64 bit version I could not find it anywhere but a little digging through the MYSQL bug database produced :

Explanation: This is not a bug.
MyODBC 3.51 is 32bit application which, by default, goes into
%SystemRoot%\sysWOW64 folder on 64bit windows. The DataSources(ODBC) link in
control panel searches for 64bit drivers in %SystemRoot%\system32 folder (silly
naming I know…).
What you have to do is:
  Locate odbcad32.exe in *%SystemRoot%\sysWOW64 folder* (DO NOT use the one in
%SystemRoot%\system32 folder pointed to in control panel!)
  Use this program instead of default 64bit version to access MyODBC
  Start it and add MyODBC driver from the list of available drivers

Even though I was installing Version 5.1.6 the above applied to that to and following the above suggestions resolved the issue.

So now that we had a working connection between VFP and MYSQL it was time to do some performance tuning. After all we want things to go nice and snappy. There is awfully little to be found on VFP/MYSQL connections so I spent the better part of a day testing and experimenting. The best settings and procedures I came up with are.

• On the VFP side use shared connections. It can take up to 1/2 second to set up a connection. Plus setting up individual connections will unecessarily increase the connection count on the server. For large record sets (>100 records) make that async. shared connections.To connect with vfp use:

  gn_conn = SQLCONNECT(“MYSQLDSN”, .T.) && Establish A shareable connection

  gn_con2 = SQLCONNECT(gn_conn) && for each subsequent connection to share that connection

  to make the connection async

  ll_success = SQLSETPROP(ln_conn,”Asynchronous”,.t.)

  And then to execute a SQL statement

  ll_success = SQLEXEC(gn_conn, lc_sql, lc_alias)

  * lc_sql holds the MYSQL SQL statement and lc_alias is the alias of the receiving cursor

  Of course the gn_conn could becone gn_con2 etc depending which shared connection you use. The advantage of using a shared connection is that you can run multiple statements asynchronously at the same time. So lets assume we have 3 SQL statements that we need to run on form LOAD then shooting them off on 3 shared asynchronous connections will take about ½ second before returning control back to our program. We now can go on and continue with our code while the results from our SQL statements are loaded in the background. This prevents long wait times – especially in slow connections.

 

• On the ODBC connector use the following settings (accessible by clicking the Details button on the ODBC configuration screen):Allow Big Result Sets = on

  Use Compression = on (will drastically improve availability of data on large result sets

  Disable Driver Provided cursor support = on (performance increase don’t know why)

  Force use of forward only cursors = on (performance increase)

  Allow multiple statements = on (performance)

  There is another setting that drastically improves performance but it comes currently at a steep price.

  Don’t cache results of forward only cursors = on

  If you use this option you cannot use asynchronous and shared connections as you will receive an error from the ODBC driver that states: ““You cannot execute that command now – commands out of sync SELECT ……” or something similar. I have seen some bug reports filed on this and am hopeful that this gets resolved at some future time.

Next: Transfering the Database

PlanetMySQL Voting: Vote UP / Vote DOWN

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 price of todays Hard disks. But it introduced quite a few new problems.

1. VFP tables are limited to 2 Gb space. So if one wants to store multiple years of data one has to sooner or later make some hard decisions. Like do we archive some of the data, Do we split the table horizontally or vertically etc.
2. Performance: VFP is based on Flat files. So if the table(s) and index files are big it takes considerable amount of time to send them to the workstation over a network. The code resides usually on a server with the data. So the workstations access the code files from the server and then they access the flat data/index files. All resulting in a considerable amount of network traffic. The bigger the tables get the slower the system gets noticeably.
3. Difficulties with access over the Net. Yes one can use VFPOLE and a web server or .Net. Or pcAnywhere or Logmein. But why duplicate a screen as a web page or tie up one or multiple computers with a remote access software. Because using VFP over a VPN over the net is out. I tried it just for “fun” once and printing an invoice over a 256kb DSL connection took 4 Minutes
4. Microsoft is discontinuing VFP. Granted there is a chance of them changing their minds and there are some people around that are working on projects that will compile VFP code into .NET or other platforms. BUT…. are they going to complete it? Are they going to stay around? Are they going to address the 2GB table size restrictions? And then we are going to do a rewrite in .NET….

So especially in view of points above I made the decision to move on and find a new platform. So my first idea was to go to MS SQL. There are upsizing wizards for that included with VFP and after all I wrote quite a few things for MS SQL in the late 90’s namely the whole backend for a day trading system including real time stock analysis etc.

So then how did I get here to MYSQL?

MS SQL fell apart mostly because of what I call financial realities and how it worked out also – ease of use – at least in my case. Granted Microsoft gives the express edition away for free. But its pretty useless in the above scenario as a 4Gb Database limit is worse than a 2GB table size limit. And people like to store a lot of data…. Plus Microsoft has the tendency of forcing you to upgrade. I bought Office 2000, it worked fine for me. Now I got a Vista machine and a Windows 7 Laptop and office 2000 will not even install… So now I am using Open Office…

So I laid out a plan for the migration.

1. Move the VFP data onto a MYSQL Server
2. Split the VFP code into Client/Server portion and write the MYSQL procedures and Triggers to pretty much equal the ones in the VFP database
3. Keep VFP as a front end for now and alter the form code to access the remote MYSQL database instead of the Foxpro database. This should allow to take care of the aforementioned problems and overcome the size and performance limitations that currently exist with the use of VFP plus allow good performance in accessing the data over the Internet/VPN
4. In the future if necessary leave VFP and turn the whole application into one that runs on PHP/APACHE/MYSQL. This would allow great flexibility as to available platforms to run on and allow any device capable of “doing websites” to access the data.

Next Chapter: “First Contact” Setting up VFP and MYSQL to interact

PlanetMySQL Voting: Vote UP / Vote DOWN