This is driving me CRAZY. Simplest MySQL Query not working.

[lpxxfaintxx]

			
$finalto = $user[to];
$from = $messagefrom;
$subject = $_POST['subject'];
$message = $_POST['message'];
$time = date("F j, Y");

	$query3 = mysql_query("INSERT INTO `pms` (from, to, subject, message, time) VALUES ('$from', '$finalto', '$subject', '$message', '$time'") or die(mysql_error());

 

Seriously, did I do something wrong, or am I just going crazy?

 

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from, to, subject, message, time) VALUES ('5', '6', 'heyy', 'supp', 'January 29,' at line 1

[Mchl]

http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

[lpxxfaintxx]

I've tried everything, still not working. =/

 

 

 

$query3 = mysql_query("INSERT INTO pms (`from`,`to`,`subject`,`message`,`time`) VALUES (`$from`, `$finalto`, `$subject`, `$message`, `$time`") or die(mysql_error());

 

$query3 = mysql_query("INSERT INTO pms (`from`,`to`,`subject`,`message`,`time`) VALUES ('$from', '$finalto', '$subject', '$message', '$time'") or die(mysql_error());

 

$query3 = mysql_query("INSERT INTO pms (`from`,`to`,subject,message,time) VALUES ('$from', '$finalto', '$subject', '$message', '$time'") or die(mysql_error());

[Mchl]

Still same error?

 

[edit]

 

you have a missing parentheses at the end of the query (before "or die")

 

Try

 

$query3 = mysql_query("INSERT INTO pms (`from`,`to`,`subject`,`message`,`time`) VALUES ('$from', '$finalto', '$subject', '$message', '$time')") or die(mysql_error());

[cwarn23]

Does this include everything?

$query3 = mysql_query("INSERT INTO `pms` SET `from`='".addslashes($from)."', `to`='".addslashes($finalto)."', `subject`='".addslashes($subject)."', `message`='".addslashes($message)."', `time`='".addslashes($time)."'") or
die(mysql_error());

Also when retrieving the values, say they were fetched from the database as an array called $row, you would need to do the following:

$row['from']=stripslashes($row['from']);
$row['to']=stripslashes($row['to']);
$row['subject']=stripslashes($row['subject']);
$row['message']=stripslashes($row['message']);
$row['time']=stripslashes($row['time']);

Hope that helps.

[trq]

Also when retrieving the values, say they were fetched from the database as an array called $row, you would need to do the following:

$row['from']=stripslashes($row['from']);
$row['to']=stripslashes($row['to']);
$row['subject']=stripslashes($row['subject']);
$row['message']=stripslashes($row['message']);
$row['time']=stripslashes($row['time']);

Hope that helps.

 

You don't need to use stripslashes if your data is escaped properly.

 

Also note that mysql_real_escape_string is the prefered escaping method.

[Mchl]

cwarn23: Could please explain, why are you stripping slashes from input variables, just to add them again?

[trq]

cwarn23: Could please explain, why are you stripping slashes from input variables, just to add them again?

 

I think his talking about when you go to display them from the database again. Of course, if its done properly there will be no slashes to strip.

[Errant_Shadow]

did you take a look at Mchl's link?

 

try changing the names of your sql fields. avoid names like "from" and "to" because SQL uses these words for the query structure.

[Mchl]

He's put them into backticks ``. As long as he remembers to do that, they will not mess up anymore. There was a missing ) at the end of the query, and if lpxxfaintxx fixed it, it should work fine.

[Errant_Shadow]

Ah, there is it. I have a question about sql queries if you have another moment.

 

I've seen people enter variables into queries with periods around them like this '.$var.'

Does this method function differently than writing it without them? ('$var')

[cwarn23]

cwarn23: Could please explain, why are you stripping slashes from input variables, just to add them again?

There is a very good reason for this which must be known for every mysql query with insert/update data containg quotations. Below is an example of what you would have done to assign a variable to a column:

"`message`='$message'"

But there are 2 problems with that code. First is that in most cases, the string $message will be submitted to the database instead of what is inside the variable. To solve that you just place an exterior quotation then a dot to connect then the variable then another dot to reconnect then another exterior quotation. The exterior quotation is the quotation mark that surrounds the entire query.

 

"`message`='".$message."'"

That leaves us with the above code. Then there is your question about why use the addslashes function when you are going to remove them. That is because what if the variable contains a quotation mark? If there is a quotation mark in the variable it will escape one of the quotation marks in the mysql query making a fatal error. Below is a basic example of the error:

$message="asdf'asdf'asdfa'aja\"ads";
"`message`='".$message."'"

With those quotation marks in the variable, for sure it will make the mysql query fail. But if you use the addslashes command (or remove quotation marks completley), it will solve that for you. However if addslashes is used, to get the quotation marks in their origional format you need to use stripslashes.

Any other questions.

[Mchl]

There is a very good reason for this which must be known for every mysql query with insert/update data containg quotations. Below is an example of what you would have done to assign a variable to a column:

"`message`='$message'"

But there are 2 problems with that code. First is that in most cases, the string $message will be submitted to the database instead of what is inside the variable.

 

Wrong. Variables in double quotes are evaluated to their values.

 

Then there is your question about why use the addslashes function when you are going to remove them. That is because what if the variable contains a quotation mark? If there is a quotation mark in the variable it will escape one of the quotation marks in the mysql query making a fatal error. Below is a basic example of the error:

$message="asdf'asdf'asdfa'aja\"ads";
"`message`='".$message."'"

With those quotation marks in the variable, for sure it will make the mysql query fail. But if you use the addslashes command (or remove quotation marks completley), it will solve that for you. However if addslashes is used, to get the quotation marks in their origional format you need to use stripslashes.

Any other questions.

 

That's why you should use mysql_real_escape_string instead of addslashes. You won't need to strip slashes when retrieving data from database.

[trq]

However if addslashes is used, to get the quotation marks in their origional format you need to use stripslashes.

 

No you don't. The slashes simply escape data for use in the query, they are not stored in the database.

[cwarn23]

There is a very good reason for this which must be known for every mysql query with insert/update data containg quotations. Below is an example of what you would have done to assign a variable to a column:

"`message`='$message'"

But there are 2 problems with that code. First is that in most cases, the string $message will be submitted to the database instead of what is inside the variable.

 

Wrong. Variables in double quotes are evaluated to their values.

 

Then there is your question about why use the addslashes function when you are going to remove them. That is because what if the variable contains a quotation mark? If there is a quotation mark in the variable it will escape one of the quotation marks in the mysql query making a fatal error. Below is a basic example of the error:

$message="asdf'asdf'asdfa'aja\"ads";
"`message`='".$message."'"

With those quotation marks in the variable, for sure it will make the mysql query fail. But if you use the addslashes command (or remove quotation marks completley), it will solve that for you. However if addslashes is used, to get the quotation marks in their origional format you need to use stripslashes.

Any other questions.

 

That's why you should use mysql_real_escape_string instead of addslashes. You won't need to strip slashes when retrieving data from database.

==AND==

However if addslashes is used, to get the quotation marks in their origional format you need to use stripslashes.

No you don't. The slashes simply escape data for use in the query, they are not stored in the database.

 

 

 

 

I did a few little tests with mysql query's and I found that the following mysql query inserts the data (not variable name) and does not need the use of the stripslashes function.

$var="aaa'aaa\"aaa'aaa";
$var=addslashes($var);
mysql_query("INSERT INTO `table` SET `field`='$var'") or die(mysql_error());

So those in the above quotes of this post were right except the part about mysql_real_escape_string() which is optional if you are using addslashes(). However to use the addslashes function, unless you use it on the variables before starting the mysql query, the code will be as follows:

$query3 = mysql_query("INSERT INTO `pms` SET `from`='".addslashes($from)."', `to`='".addslashes($finalto)."', `subject`='".addslashes($subject)."', `message`='".addslashes($message)."', `time`='".addslashes($time)."'")
or die(mysql_error());

And as I said in this post (and thorpe said earlier), you do not need to use stripslashes() at all. Also if you really wanted just the one set of double quotes for the mysql query, you could also use the following:

$from=addslashes($from);
$finalto=addslashes($finalto);
$subject=addslashes($subject);
$message=addslashes($message);
$time=addslashes($time);
$query3 = mysql_query("INSERT INTO `pms` SET `from`='$from', `to`='$finalto', `subject`='$subject', `message`='$message', `time`='$time'")
or die(mysql_error());

Hope that helps.

[Mchl]

Actually mysql_real_escape_string is recommended over addslashes when escaping variables for MySQL queries. It's been designed to do this task. For example it will use current database connection encoding when escaping (addslashes just doesn't know about any database connections, so it doesn't care)

[tran_dinh_ba]

You missed out one closed bracket after '$time'

$query3 = mysql_query("INSERT INTO `pms` (from, to, subject, message, time) VALUES ('$from', '$finalto', '$subject', '$message', '$time')  ") or die(mysql_error());

 

[BioBob]

stripslashes, as far as security goes, does NOT out rank mysql_real_escape_string because other language and character sets have characters that will behave like slashes but stripslashes will NOT catch them.  Trust me, use mysql_real_escape_string instead.

 

require_once("../../inc/db.php");

 

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

 

$query = "whatever...

 

alternatively, if you do decide to use stripslashes, it is recommended to use it with htmlspecialchars as it will change the character set and prevent security hole of still being open to sql injection attacks.

 

stripslashes(htmlspecialchars($var,ENT_NOQUOTES,'ISO-8859-1'));

 

Basically a \ or a ' in ISO-8859-1 is not the same as a \ or a ' in another character set, means that without real_escape_string, you are still vunerable to SQL Injection.  Stripslashes will miss these!!

 

----

 

Now, in regards to the ORIGINAL post, it just looks to me like the sql syntax is off on placement of a ) character.

 

it should be:

$query = mysql_query("INSERT INTO `tablename` (`row1`,`row2`) VALUES ('$val1','$val2')") or die (mysql_error());

your code looked like this:

$query = mysql_query("INSERT INTO `tablename` (`row1`,`row2`) VALUES ('$val1','$val2'") or die (mysql_error());

 

See the difference?

 

Also as a suggestion, it sometimes helps to see the original query as it is being thrown to the mysql query.  Very fast tutorial.  A Period is a "concatination" character.

 

$string1 = "abc";

$string2 = "123";

$string_together = $string1 . $string2;

 

returns "abc123";

 

Here's why I suggest that.  In your DIE command, you can string a couple of things together.  I suggest doing it like this:

 

or die("MySql Error: " . mysql_errno() . "<br />\n$query<br />\n" . mysql_error());

 

This way it gives you the Error Number, your original query as it was thrown to mysql_query to begin with, and what mysql was complaining about, like being bloated or feeling neglected, oh wait, thats my wife, nevermind.  Now the important part here is that if you can see your original query, you'll have a much better chance of figuring out what the problem is, over trying to figure out in the $query =  string where the problem lies, and trying to see which sets of parenthesis are being called by mysql and which ones are used by php.  Oh I missed a quote at the end of a vairable.  It might not stick out so easy in your original string but when you see whats thrown at mysql_query, its just easier to read.

 

Also, for god sake, do NOT use that code on a PRODUCTION box.

[Mchl]

... like being bloated or feeling neglected, oh wait, thats my wife, nevermind. 

 

Just set error_reporting to 0 and you're done with her. XD