Preparing to talk to the ASP script

I'll start out by showing you how to prepare user input from your Flash application to be used to query the database. Before this data is sent to the ASP script, it needs to be converted to XML and formatted so that it is stored correctly in the MySQL database. First you'll need to make sure that date objects and user input data can be translated into something MySQL will understand. Then you'll need to wrap up the SQL calls into properly formatted XML for the server-side ASP script.

Making a date MySQL-friendly

When sending any data to a database, it needs to be formatted it in a way that the database expects to receive it. One of the things I discovered when working with MySQL is that it is very particular about how it wants dates formatted. It turns out that MySQL expects the date in the format of year, month, day (yyyymmdd). One catch to this is that MySQL expects leading zeros on the single-digit months and days. So if you were trying to submit the 9th of August, 2007, "200789" would not be valid but "20070809" would.

To make it easier, I created a function that takes a Flash Date object and converts it into a MySQL-friendly string. Look at the makeMySQLDate ActionScript function below to see how it works:

public function MakeMySQLDate(tmpDate:Date):String
{
   var iYear:Number = tmpDate.getFullYear();
   var iMonth:Number = tmpDate.getMonth()+1;
   var iDay:Number = tmpDate.getDate();
   var tmpStrMonth:String = iMonth.toString();
   if (iMonth <= 9) {
      tmpStrMonth = "0" + tmpStrMonth;
   }
   var tmpStrDay:String = iDay.toString();
   if (iDay <= 9) {
      tmpStrDay = "0" + tmpStrDay;
   }
   return iYear.toString() + tmpStrMonth + tmpStrDay;
}

As you can see from the code above, the makeMySQLDate function takes a Flash Date object as a parameter and returns a string. To create the string, it pulls the Date object apart into year, month, and day. The function then looks at the month and day and, if necessary, adds a leading "0." Finally, it concatenates the data into a string in the "yyyymmdd" format and returns it.

Now you need to make sure the data does not have any characters that could cause problems when creating the SQL statement.

Creating MySQL-friendly data

One common problem with submitting data to a database is that the raw data may contain characters that are not themselves valid when put into a SQL statement. One example of this is the single quote. In a SQL statement, a single quote indicates a closing of variable data; for example, Booga='Data Here'. The single quote becomes a problem only when you take data from user input and don't take this into account.

For example, a user types in a comment in a text input that says, "Can't get the server data." You then take this text and place it directly into a variable in a SQL statement like Comment='Can't get the server data.'. As soon as you submit it, you get an error because you have more than two single quotes around the data. To solve this problem, I created the fixStringDataForMySQL function:

public function fixStringDataForMySQL(strTemp:String):String
{
   var i:Number = 0;
   var iOld:Number = 0;
   var firstQuote:Boolean = false;
   var strNew:String = "";
   strTemp = trim(strTemp);
   while (i != -1){
      i = strTemp.indexOf("'", i);
      if (i != -1){
         if ((strNew != "") || (firstQuote)){
            strNew += "\\" + strTemp.substring(iOld, i);
         }
         else if (i != 0) {
            strNew = strTemp.substring(iOld, i);
         }
         else {
            firstQuote = true;
         }
         iOld = i;
         i++;
      }
   }
   if (iOld <= strTemp.length){
      if (strNew != ""){
         strNew += "\\" + strTemp.substring(iOld, strTemp.length);
      }
      else {
         strNew = strTemp.substring(iOld, strTemp.length);
      }
   }
   return (strNew);
}

This function traverses the string parameter supplied and adds the "\\" characters in front of the single quotes to escape them so that the database does not interpret them incorrectly. Once it is done traversing the string, escaping the single quotes, it returns the modified string.

Now you're ready to see how to wrap this data up in a SQL statement.

Creating the SQL statement

Once you fix all the dates and string data so that it is MySQL-friendly, you need to put together the SQL statement that will be stored in XML. This article assumes that you have a basic knowledge of SQL statements relating to MySQL, so I will not go into too much depth about the exact syntax. Instead, here's a basic SQL statement that uses the functions discussed above to fix the data:

SQL = "SELECT * FROM myTable WHERE Comment=' fixStringDataForMySQL(strComment)'
    AND RecDate='makeMySQLDate(RepDate)'"

This SQL statement is saying, "Get all of the records where the Comment field matches strComment and the RecDate field equals RepDate." By using the two functions that make the data MySQL-friendly, you can be sure that you will minimize the conflicts that can occur.

Now you can package the SQL statement into XML that the server-side ASP script understands.

What about SQL injection using this technique?

One common problem that can occur when users are allowed to enter data that is sent to a database is called SQL injection. This is a technique where a user put a SQL statement into an input control in the hopes of gaining access to the database. To protect against this, make sure users cannot corrupt your SQL statements, which could confuse the database into executing a malicious statement.

One technique to prevent this situation is to escape characters just like the fixStringDataForMySQL function does. This solution is not completely bulletproof, however, because it is escaping only the single quote—but it works for most situations.

So this takes care of string problems but users can also try to hack their way in by using numeric or date controls. To combat this, you should make sure that the input from these types of controls are truly numeric and dates. Don't just blindly take user input and stick it into your SQL statement without making sure it is the proper type or escaping out offending characters.

This article was not designed to cover all the ways you can prevent SQL injection from occuring. If you are concerned about the potential threat to your database from this type of attack, I recommend that you read up on all the methods to prevent SQL injection. A good place to start is Wikipedia's entry for SQL injection. You can also do a Google search for SQL injection.

Understanding XML schema for the data sent to the server-side ASP script

Once you create a properly formatted SQL statement, it needs to be wrapped up into XML so that it can be sent to the ASP script. Here's the schema of the XML that will be sent to the ASP script:

<MySQLRequest>
   <SQL SQL='<SQL DATA>' Name='<DATA LABEL>'></SQL>
   <SQL SQL='<SQL DATA>' Name='<DATA LABEL>'></SQL>
</MySQLRequest>

where:

• SQL holds the SQL call that will sent to the MySQL database.
• Name holds the name for the data that is returned by the SQL call from the MySQL database. The importance of this becomes clearer later in this article; it enables you to know which SQL statement returned that data so you can parse it correctly.

Now that you understand the schema, you are ready to begin creating the XML.

Making data XML-friendly

It would be great to simply create a string and then add that to the XML and have it work. Unfortunately, that is not the case. Like SQL statements, XML does not like single quotes very much either. To remedy the problem, I created the fixSingleQuotes function, which takes a string and converts it so that single quotes are properly formatted for XML:

public function fixSingleQuotes(strTemp:String):String
{
   var pattern:RegExp = /\x27/gi;
   return (strTemp.replace(pattern, "'"));
}

This function simply finds all the single quotes and replaces them with the XML-friendly character entity, &apos. To do this, the function creates a regular expression object:

var pattern:RegExp = /\x27/gi;

The object has the single quote in it (/x27/; this is the ASCII Hex value for the single quote). Then the code specifies to replace all instances (g means "replace all matches") and ignore case (i means "evaluate without case sensitivity"). The function then calls the replace method of the string with the regular expression pattern and specifies that, when it finds a single quote, to replace it with the "'" string and return resulting modified string:

return (strTemp.replace(pattern, "'"));

Now that you've fixed the single quotes, you are ready to create the SQL XML.

Packaging the SQL statements into XML

To make the creation of the XML easier, I created the createSQLXML function:

public function createSQLXML(SQL:String,
    DataLabel:String):String
{
   return ("<SQL SQL='" + fixSingleQuotes(SQL) + "' Name='" + DataLabel + "'></SQL>");
}

This function uses the fixSingleQuotes function to fix any single-quote issues that may exist in the SQL string, and then package the string into XML that the ASP script will undertand. This example shows how to bring all this together to format the SQL and package it up into XML:

var SQLXML:String = "";
SQLXML = createSQLXML("SELECT * FROM MyTable WHERE Name='Craig'", "Product");

This code calls the createSQLXML function and then assigns the resulting XML to the SQLXML variable. Here is what is returned from the createSQLXML call:

<SQL SQL='SELECT * FROM myTable WHERE Name=&apos;Craig&apos;' Name=&apos;Customer&apos;'></SQL>

Notice how the single quotes have been modified so that the XML does not confuse them for other attribute names or data.

You do not have to just send one SQL statement to the database. If you want to send more than one, just concatenate the results of the createSQLXML calls into one variable. Here is one example:

var SQLXML:String = "";
SQLXML += createSQLXML("SELECT * FROM MyTable WHERE Name='Craig'", "C_Product");
SQLXML += createSQLXML("SELECT * FROM MyTable WHERE Name='Susan'", "S_Product");
SQLXML += createSQLXML("SELECT * FROM MyTable WHERE Name='Giada'", "G_Product");

This batches up the SQL statements so that the database processes all three and then sends back the results of all three in one XML package.

Now that you have seen how to package up the SQL calls into XML, find out what's involved in sending them to the server-side ASP script.

Page 2 of 4

Previous Page

Next Page