Very strange problem. The String passed to URLVariables.decode() must be a URL-encoded query string containing name/value pairs. [HELP]

[Applauz]

I am having a strange issue.

I'm reading SQL through a PHP file. Everything was working fine... then all of a sudden the account that sits in the first slot on the database is getting this error every time it tries to read the SQL.

Error: Error #2101: The String passed to URLVariables.decode() must be a URL-encoded query string containing name/value pairs.

However every other account works fine. This is only happening for account in slot #1 ...  any idea why this would start happening or how I can fix? It's driving me nuts.

[kglad]

the data got corrupted in that first slot?

inspect your table/line for an error.

[Applauz]

So when I do a test trace out

trace("THE SQL DATA AS A WHOLE IS: " + event.target.data);

This returns back every single SQL value for the user .. and every entry is there.

However if I try to trace any of the individual entries like this.. it fails.

  trace(event.target.data.parentIDResult);

and it only happens with the account that is in ID #1 on the database....  ID #2 and up are fine... thousands of other accounts are fine. 

What specifically do you recommend I do ?

[kglad]

manually inspect the problematic table.

[Applauz]

OMG... I found the error.  The user of ID 1 saved a variable that contained an  & symbol.      ughhh...       How can I allow users to store these values ?   Sorry I'm not big on SQL knowledge.

kglad  wrote

manually inspect the problematic table.

[kglad]

you're susceptible to bigger problems (like deletion of all your data) if you're not validating data entry.  start by reading, https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

(p.s when using the adobe forums, please mark helpful/correct responses, if there are any.)

[Applauz]

So the problem is in this line...  if parented contains an ampersand it cannot get past this line.

$sql="SELECT parentid FROM users WHERE id='$globalID2'";

[Colin Holgate]

This might help:

How to use the ampersand (&) symbol in SQL statements as a literal value | DevNotes

[Applauz]

I believe I'm storing the ampersand properly... if I read database html / php it works fine.. its the passing of the variable that contains the ampersand in it back to my AIR app that breaks.

[kglad]

edit your db and remove that ampersand and validate data to prevent anything other than numbers and alpha characters. ie, read that link.

[Applauz]

Yes if I remove the ampersand in the database all is good.  Problem is this is a field that the user stores whatever text they want in. They could put "Peanut Butter & Jelly"   ...  I don't want to prevent users from the use of the & symbol.  

[Colin Holgate]

The link I gave uses fish & chips as the test case. I imagine it would work for peanut butter & jelly too.

[Applauz]

I don't understand whats happening at that link tho?  I don't see any examples of the code.

[kglad]

if you want to allow special characters in certain locations, escape them so they're safely encoded.

[Colin Holgate]

Here is the important bit:

SQL> set escape on;

SQL> select 'I like fish \& chips' as x from dual;

By setting escape on you can then use \& instead of &. If your parentid might have an ampersand in it, you could do:

parentid = parentid.split("&").join("\&");

[Applauz]

Am I doing this in my php file though or in actionscript ?

In my php after I select the elements of the database I set up for as3 like

$child1RewardName8Status = $data['child1RewardName8'];

and then in the print looks like this

&child1Reward8Status=$child1Reward8Status

[Applauz]

I think I'm looking in the wrong place.... when I am using html / php everything is perfectly fine and writing and being read properly.

Could it just be when the data is being brought back in to the app via this ?

print("parentIDResult=$parentID

&parent2IDResult=$parent2ID

I'm separating in the print with & symbols...   so when $parentID contains an ampersand in it ...  could this be the problem ?

[Applauz]

SO this is 100% not a problem with my SQL, Database, etc .. this is a problem with pulling a php variable back in to Animate CC.

If I build a new project and have a php file passing variable like this '

print("parentIDResult=peanut butter & jelly");  It has the same problem .. it cannot do this.

So now ... how on earth can I get this to allow an ampersand  ?   

[Applauz · Accepted solution]

So for anyone who runs in to this in the future... I managed to figure this out and solve the problem.

There are 2 key pieces to this.

In order for my app to store the data from the app with an ampersand you must use ( mysql_real_escape_string ) in your php call. ( this I already had ).

child1RewardName8='".mysql_real_escape_string($child1RewardName8)."',

This allows you to store the ampersand and special characters in your database.

When you are reading the database and passing that data back to animate cc you have to use ( rawurlencode ) in your php.

&child1RewardName8Status=".rawurlencode($child1RewardName8Status)."

[Applauz]

Things get even stranger...

When I switch from URLLoaderDataFormat.VARIABLES to URLLoaderDataFormat.TEXT;   and I trace event.target.data it traces out everything perfectly fine... including the ampersand.   

However trying to trace the specific value fails  eg. trace("parentIDResult = " + event.target.data.parentIDResult);    

parentIDResult in the database = "this & that"   for example.   So how is it that it traces fine in the event.target.data but cannot be read via event.target.data.parentIDResult     ?

[kglad]

again, i repeat: read about data entry validation.  you have no idea what kind of problems you're headed for if you allow sql injection.

do not allow ampersands or any other symbols especially quotes (') and double quotes (") unless they are expected and validated.

[Applauz]

I am using mysql_real_escape_string() on all entries to the database.

[olinh26627966]

Had the same problem just another Idea for solving it is

when sending it

URLfile = URLfile.split("&").join("AND");

urlvars.TheFile = URLfile;

and when receiving it

myfile = myfile.split("AND").join("&");

if it is a hacking problem it might help?