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

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

Now it became time to conveert some SQL statements. Even though many SELECT SQL statements will run unchanged in MYSQL it might be wise to check and see if they are performing well. There are some differences in how VFP and MYSQL optimize queries and a query that just performs great in VFP might be very slow in MYSQL. And then there are some cases where we have to replace things that are possible in VFP but not supported in MYSQL.

First lets assume that our tables all have lots of records. After all it does not pay to spend time on optimizing on tables with a few dozend records. I also will not go into VFP optimization here as this is a MYSQL blog.

Lets assume for our first case we might have a table with invoice detail records and a table with items that are on sale. So now we want to get maybe 2 queries. One to show a total of all the items that were on sale and the second a total of all the items not on sale. I will leave date selections etc out of this as I just want to focus in on some performance issues

In VFP we might have had something like

SELECT <fieldlist> FROM detail det WHERE INDEXSEEK(det.item, .f., “promotions”, “item”)

SELECT <fieldlist> FROM detail det WHERE .NOT. INDEXSEEK(det.item, .f., “promotions”, “item”)

respectively depending which set we want. In VFP it was quite fine to use and INDEXSEEK in the Where clause. I sometimes used it in place of a join as it seemed to work faster and in previous version of VFP was a way to get around limits in the number of JOINS and the complexity of a query vfp supports.

In MYSQL now you could replace the INDEXSEEK with an IN or EXISTS(SELECT….) Subquery  so the query rewritten will look something like

SELECT <fieldlist> FROM detail det WHERE EXISTS (SELECT * FROM promotions p WHERE p.item = det.item)

Or the “NOT EXISTS” version for the oposite result. You could also use IN but in some cases that might even be more inefficient as IN retrieves the whole matching subset and EXISTS stops at the first occurance.

If we run the above query through “EXPLAIN” to see what MYSQL is doing we will see that on ther “SELECT <fieldlist> FROM detail det” it will perform a table scan and  then the item  index of the promotions table. So having a large “details” table that has also an “item” index this is not very efficient. In MYSQL the way to improve on that is to rewrite the query as a join. like

SELECT <field list> FROM detail det JOIN promotions p ON det.item = p.item

In this case MYSQL will use both item indexes and only retrieve the items matching. It gets a little bit more complicated with the case where we want to select items that have no promotions but we can do a join there too with

SELECT <field list> FROM detail det LEFT JOIN promotions p ON det.item = p.item WHER p.item = NULL

This one again will make the selection on the index level and depending on the amount of records you are dealing with result in great performance gains.

Lets now look at another scenario. Lets assume we have a invoice master table with  as “balance” field and we want to filter out as part of a query all records that are open so we are looking for “BALANCE <> 0″ In vfp we created an index on balance  and that improved the statement

SELECT <fieldlist> FROM master WHERE balance <> 0

drastically. Now if we do the same in mysql and we check with

EXPLAIN SELECT <fieldlist> FROM master WHERE balance <> 0

We will find that MYSQL is doing a table scan. MYSQL seems to have a problem with “<> <keyfield>”

You can get a slight improvement by

SELECT <fieldlist> FROM master WHERE balance < UNION SELECT <fieldlist> FROM master WHERE balance > 0

As it seems MYSQL has an easier time using the index with just the > and the < and a UNION but you are running into unnecessary overhead there too. What I have found most efficient so far is to add a field to the table

unpaid BIT

And then create an index on this field and set it in the BEFORE UPDATE and BEFORE INSERT triggers with

IF NEW.balance = 0 THEN SET NEW.unpaid = 0; ELSE SET NEW.unpaid = 1; END IF;

THE sql statement now looks like

SELECT <fieldlist> FROM master WHERE unpaid = 1

I used the BIT type field as it nicely translates into a VFP logical field with .f. for 0 and .t. FOR 1. But you probably can achieve the same by using an TINYINT or CHR(1)  or BOOLEAN field.

The performance improvement was drastic.

Beyond this it is always good to remember to

1.) make sure you have indexes on the fields in the “WHERE” clause and they are being used. USE “EXPLAIN ….” in the MYSQL query editor to find out.

2.) keep the row count to a minimum especially if you are using subqueries. So it often makes sense to first cut the number of records down with a subquery. I will go into detail on this in a seperate blog

As always comments/suggestions are welcome as this is all work in progress

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

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