Proper Use of Quotes with POST?

[kuau]

Is it OK to use double quotes within the POST variable instead of the single quotes? Like this...


$sql = "UPDATE booking SET country = '$_POST["country"]',... "

Or do I have to do it like this?


$sql = "UPDATE booking SET country = ' "$_POST['country']" ',... "

Seems like a preponderance of quotes. Thanks, e

[thetestingsite]

You could use either double or single quotes, but you really should break out of the statement before entering in variables. Something like the following:



$sql = "UPDATE booking SET country = '". $_POST["country"] ."',... "


Hope this helps.

[djr33]

Actually, that's a really bad idea in the first place. Without converting the data to something you know isn't dangerous, a user could actually inject malicious code into your database.

Try something like this:


$country = mysql_real_escape_string($_POST['country']);
$sql = "blah blah $country blah";

Now, as for different kinds of quotes, here's how they work:
": this will parse the string it contains; variables ($var) will be used, not the literal text "$var"; \n \r \etc will be converted; if you want to avoid any of those, you need to escape the character, like \$var = "$var", not "val". (you will need to escape double quotes like \" within the string).
': nothing is converted and single quotes are better because they parse faster, unless you need something converted like above. (you will need to only escape single quotes like \' within the string).

[kuau]

Dear TTS and Daniel: Thanks for the info re the quotes. In the situation I described (country), the value comes from a drop-down list in the form. Doesn't that mean that the user cannot enter their own values? If so, would it be necessary to protect against SQL injection? Or do you do it only for fields in which the user can type text? Does mysql_real_escape_string do the same thing as addslashes? Thanks , e

[djr33]

Anything sent from clientside can be dangerous. There's nothing that guarantees it's an item from the list. (Instead of escaping the values you could just compare with an array of the possible allowed values, but that's more work, unless you need it to match one.)
Basically, all you need to do is create a mirror form like your page, on any webserver, switch the select to a text field and type in any sort of hack you'd like for your database.

Basically, if the user sends it, don't trust it, and escape!!

[Jas]

Anything sent from clientside can be dangerous. There's nothing that guarantees it's an item from the list. (Instead of escaping the values you could just compare with an array of the possible allowed values, but that's more work, unless you need it to match one.)
Basically, all you need to do is create a mirror form like your page, on any webserver, switch the select to a text field and type in any sort of hack you'd like for your database.

Basically, if the user sends it, don't trust it, and escape!!

I'm sure you can download some questionable extensions for web browsers that can manipulate things like form field types. HTML is processed client side, so it can be changed by the user. Only the result is sent back to the server. Daniel is definitely right. But, keep in mind that not everyone will be out to get you. When creating a website, you have to imagine that everyone will be searching for security holes, but in reality, almost no one will be.

[kuau]

Are you saying that for every single variable on a form I should do this before I do anything else with them?


$variable = mysql_real_escape_string($_POST['variable']);

Are you supposed to put backticks around every reference to a fieldname and a table name? eg. INSERT INTO `customers` (`id`, `name`, etc

Thanks! e

[Jas]

Are you saying that for every single variable on a form I should do this before I do anything else with them?


$variable = mysql_real_escape_string($_POST['variable']);

That, and possibly more. It all depends on how secure you want your code to be.


Are you supposed to put backticks around every reference to a fieldname and a table name? eg. INSERT INTO `customers` (`id`, `name`, etc

Thanks! e
Convention seems to be to use back ticks, but as far as I know you don't need them. I only recently started using them myself.

On the insert statement, though, it should be:

INSERT INTO `table` VALUES ("value","value","value");

Values are always in double or single quotes (usually double).

Queries would look like this:

SELECT `id` FROM `table` WHERE `user` = "cool"

[kuau]

So is this wrong??:


$sql = " INSERT INTO users
( `user_id`, `name`, `email`, `status`, `visdate`, `visit`, `confdate`, `pickdate`, `dropdate`, `remind`, `act`, `survey` )
VALUES
( '', '$name', '$emailaddress', 'I', '$today', '1', '0000-00-00', '$pickdate', '$dropdate', '0', '0', '0' ) ";

That's the part that works! This is the part that doesn't and I can't see why and am ready to shoot myself:


if ($source == "cp")
{
$sql = " UPDATE `users`
SET status = 'B', confdate = '$today', pickdate = '$pickdate', dropdate = '$dropdate'
WHERE email = $emailaddress ";
}
else // if source from price check pages
{
$sql = " UPDATE `users`
SET status = 'I', visit = (visit + '1'), visdate = '$today', pickdate = '$pickdate', dropdate = '$dropdate', remind = '0', act = '0', survey = '0'
WHERE email = '$emailaddress' ";
}
$result = @mysql_query($sql,$connection) or die("Couldn't execute $sql query.");

[Jas]

Oh! Ignore me. I didn't know that you were specifying the columns. Yes, that is the correct way to do it-- better then how I do it actually.


" UPDATE `users`
SET status = 'B', confdate = '$today', pickdate = '$pickdate', dropdate = '$dropdate'
WHERE email = $emailaddress ";

You left the quotes out from around $emailaddress

and depending on context, shouldn't this: visit = (visit + '1') be this: visit = (visit + 1) ?

[kuau]

I tried it with and without quotes and it still doesn't work. :(

[Jas]

Echo out the query and past it here. That will show us any syntax errors.

[kuau]

Dear Jas: I hate to look like even more of a dummy, but how do I "echo out the query?"

I figure it must involve using the echo command but not sure where.... thanks, e

[codeexploiter]

I hate to look like even more of a dummy, but how do I "echo out the query?"

Echo out means you can use either print or echo to send whatever value you've stored in your variable which supposed to hold an SQL statement. For eg:



$sql = " UPDATE users SET status = 'B', confdate = '" . $today. "', pickdate = '" .$pickdate. "', dropdate = '" .$dropdate. "' WHERE email = '" .$emailaddress."'";
print $sql;


Another problem I found is in your another SQL statement, which is following


$sql = " UPDATE `users`
SET status = 'I', visit = (visit + '1'), visdate = '$today', pickdate = '$pickdate', dropdate = '$dropdate', remind = '0', act = '0', survey = '0'
WHERE email = '$emailaddress' ";

Leave the quotes issue for the time being and update it based on the above mentioned demo query. You don't need to enclose numbers within single quotes if they are number based items in your database. In other words the usage of quotes for enclosing the field value purely depends upon the data type that you use in the database. Consider an example a table with two fields one string and one number, for updating a record in such a table would be like the following:


$sql = "update mytable set name='codeexploiter', age=33 where name like 'codeexploiter'";

Another problem in your above SQL is the way you increment the field value visit, which will result in error. If you want to insert a value incremented by the existing value then first you have to query the value using another database operation store it in some variable and using that increment it in the SQL statement.

[kuau]

Dear Jas: It's working!!! Thanks a million! I am ecstatic. I spent the entire day yesterday fighting with that one and now I can move on to what I was trying to do with it in the first place. Thank you for lending your eyes and straightening out my quote confusion. I read what Daniel wrote over and over but couldn't quite grasp when to use quotes or not until you showed me examples with my own data. Much appreciation to you, TheTestingSite, and Daniel.

Yahoooooo!!!! e :)