simple INSERT to mySQL from PHP

[Seanx]

I can't seem to get my tiny Flash app to upload a simple string from flash to a database.

here is my Flash code:


post_mysql = "http://www.mysite.com/folder/post.php";
function SEND(X) {
var z = new LoadVars();
z.message = (X);
z.load(post_mysql+"?"+z.toString());
z.onLoad = function() {
trace("loaded");
};
}


and here is the code I use to call the function:


STRING = "some string";
SEND(STRING);


and here is my PHP file:


<?
$host = 'myhost';
$dbuser = 'dbUsername';
$dbpass = 'dbPass';
$dbname = 'dbName';
$table = 'sampleTable';
$user = 'sampleUser';
$date = 'today';
// connect
$db = mysql_pconnect($host,$dbuser,$dbpass) or die("output=false");
mysql_select_db($dbname) or die("output=false");
// get flash variables
$message = $HTTP_GET_VARS['message'];
// insert to mySQL
$query = "INSERT INTO $table (user, date, result) VALUES ('$user','$date','$message')";
$sql = mysql_query($query,$db);
?>


Can anyone spot what's wrong here? I know the database info is correct, the PHP file is uploaded to the correct location. If I go to phpMyAdmin and manually execute this query it works fine:

INSERT INTO sampleTable( user, date, result ) VALUES ( 'guy', 'today', 'result' )';

So why can't Flash not uploading the string to the database?

[yell0wdart]

Are you concantenating your string correctly?


$query = "INSERT INTO " . $table . " (`user`, `date`, `result`) VALUES ('" . $user . "', '" . $date . "', '" . $message . "')";

[Seanx]

Are you concantenating your string correctly?


$query = "INSERT INTO " . $table . " (`user`, `date`, `result`) VALUES ('" . $user . "', '" . $date . "', '" . $message . "')";
//
//
//



I'm pretty sure I already tried that in the myriad attempts I've made to get this to work but I'll try it again - brb

[Seanx]

hahaha omg you are a genius haha - oh thank you :D :D :D

[yell0wdart]

LOL. I don't know if I'd go that far. Glad you got it working. :)

[mattkenefick]

LOL. I don't know if I'd go that far. Glad you got it working. :)

The answer to this question has nothing to do with concatenating.
In PHP, use of double quotes renders the variables inside.


$string = 'matt';

echo '$string';
// returns $string

echo "$string";
// returns matt




The problem he was having is that he wasn't defining his fields properly. He said


... (user, date, result) ...


"Date" is a defined function in MySQL so therefore you have to use slant quotes to tell the database it is a field name, not a function. this is the correct way to define fields:


... (`user`, `date`, `result`) ...

[yell0wdart]

Good to know. I dabble more in PHP and MySQL more than anything. If it's not .NET, I don't work with it on a day to day basis. Such is the life of a .NET developer. ;)

I figured it'd be something like that, though. Best practices for any sort of SQL would be wrapping column and table names in those characters to escape table/column names (t-sql it's [column], mysql it's `column`).

That's good to know about PHP. I primarily write C#, which aside from datatypes, single or double quote has no functional difference. The compiler will complain if you try to use them interchangably. You wrap strings literals in double quotes and char literal values in single quotes. So double quotes in PHP kind of work like the string.Format() method does in C#. It's kid of a nubish, silly little thing not to know, but I'm actually kind of stoked that I know that now. LOL

[jsebrech]

Please, please, stop concatenating together your queries. Don't do it for me, do it for your users, so their data won't get stolen.

http://www.actionscript.org/forums/showthread.php3?t=164162

[yell0wdart]

I'm as big a fan of procs as the next guy. In fact, when they're available, I'll agree, there's not good reason to not use stored procedures. Parameterized queries are always more secure. However, in this case, a couple quick method calls inside the SQL is all he really needs to make his insert statement more secure.


// I'll assume he'd be creating his table name from a pre-defined subset of tables and not based on user input.

$query = "INSERT INTO " . $table . "
(
`user`,
`date`,
`result`
)
VALUES
(
'" . mysql_real_escape_string($user) . "',
'" . mysql_real_escape_string($date) . "',
'" . mysql_real_escape_string($message) . "'
)";

// mysql_real_escape_string() escapes all special characters, even hex-encoded, as far as I've read.


Dynamic SQL can be very dangerous when not properly handled. Stored procedures are an effective way of optimizing your database performance and securing your data. It's also generally good practice to keep all the SQL stored on the server. However, the vast majority of shared web hosts don't offer it.

[jsebrech]

My point is that concatenating together queries is a bad habit, even if it's possible to do it securely.

[yell0wdart]

And mine is that, while generally better practice and more secure, stored procedures aren't always available on shared plans of the vast majority of web hosts out there... so while not the perfect option, dynamic SQL is a necessity for a lot of developers. ;)

[jsebrech]

You don't need stored procedures, you need variable binding. Variable binding is a standard feature of PDO on PHP5. I don't think many hosts have PDO disabled (though I could be mistaken). Calling the raw mysql functions is bad practice in itself, and should be avoided whenever possible.

I blame it on non-updated tutorials. If all tutorials explained everything using PDO and variable binding instead of the raw mysql functions, things would be very different with database security on PHP. As it stands, most of the "quick hack" PHP scripts I see leave the database wide open to hackers, because people just can't be bothered to escape each value correctly.

[yell0wdart]

Yea, that would make sense. A problem most strongly typed languages don't have. The runtime environment will implicitly cast most native datatypes to match the SQL parameter. But I do see your point now, and completely agree. Forcing your PHP variables to match their intended SQL type is a very good thing.

Another thing about PHP I wasn't aware of. Sometimes, dynamically typed languages, like PHP, cause un-needed headaches. heh

[jsebrech]

SQL injection can happen just as easily with static typing:

If you have:
statement = "SELECT * FROM users WHERE name = '" + userName + "';"

And somehow a user can enter a userName like:
a';DROP TABLE users; SELECT * FROM data WHERE name LIKE '%

Your users table will get dropped. Even the slightest lack of attention when escaping values will let one of these get through, leaving the script wide-open to database hacking. This is how visa card details often get stolen from commercial sites. The solution with using PDO, Zend_db or something comparable, is that by using variable binding the framework always correctly escapes each value for the programmer, so it's not possible to forget it anymore.

[yell0wdart]

Very cool stuff. I didn't realize PHP had that sort of functionality yet. Simailar to what MS does with LINQ, in a sense. Definitely better than having to manually escape everything. Me like. :)