More problems dealing with quote marks

[murfitUK]

Following on to a previous enquiry about dealing with text input through an html form.Got a text input called line1. This posts to a php script. I have used the function stripslashes to deal with a problem if anyone used a single quote. This was if someone typed in eg I can't do that it displayed as I can\'t do that.Using $line1=stripslashes($_POST['line1']) solved that particular problem and echo($line1) printed I can't do that just fine.Now I am trying to get this string into a mysql database along the lines of:$query = "INSERT INTO table fieldname1 VALUES \"{$line1}\";";which works OK until a quote mark " is included in $line1. When the query is run eg:if (!($result = mysql_query($query, $connection))) showerror();it comes up with the error 1064 (I think) check your syntax near to ....Can someone suggest a solution. Thanks.

[justsomeguy]

Use the mysql_real_escape_string function to accomplish that. This function should be used for all form data inside SQL queries:

$query = "INSERT INTO table (fieldname1) VALUES ('" . mysql_real_escape_string($line1) . "');";

:ph34r:

[reportingsjr]

You have quite a few errors, and im confused on what your doing. Why are you trying to escape those quotes? Just use single qoutes.The correct syntax for what you are doing is:

$query = "INSERT INTO `table` 'fieldname1' VALUES ('" . mysql_real_escape_string($line1) . "')";

You dont need a semi colon ( ; ) at the end of queries, although you are supposed to write them that way, since they are single function things it doesnt matter so I dont recommend it.

[murfitUK]

Thanks for the quick replies.Good point about why was I escaping the double quotes so I've changed them all to single quotes. This now works OK if a double quote mark is used in form input line1. But now get the 1064 error message if line1 includes a single quote mark.I have echoed $query before running it and if line1 is I can't do that then $query prints out as:INSERT INTO table 'line1' VALUES ('I can't do that')Error 1064 : 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 't this work?'I think this was why I used double quotes around the field name (hence the escaping) but I've sort of lost track of all the changes I've made since starting this project.Any suggestions?Thanks for your help.

[MoZo1]

1. Use addslashes and stripslashes in pairs, so if you have stored something with slashes, you have to get back the original form. So again: convert the data into a storeable format, then don't forget to reverse the convertion when reading it! Fx. you get can't by input, then you can only store can\'t, but then you have to give back can't as output.2. Check the corresponding SQL syntax.fx.:

INSERT INTO employees (Lastname, Firstname, Title) VALUES('Hively', 'Jessica', NULL);

3. This:

function sql($txt, $db) //sql with error handling, $db=sql-connection	{	$result = mysql_query($txt, $db) or die ('Mysql error: ' . mysql_error() . '<br /> Original line: ' . $txt);	return $result;	}

I haven't tested it, it should be correct. *EDIT: mysql_real_escape_string is a variation of addslashes, usually you can use it with stripslashes. The same bug can occur, but with very rare characters only. But beware it in the sql's LIKE command!!

[justsomeguy]

mysql_real_escape_string does more then addslashes:

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

Again, just use this line to build your query. Make sure you use mysql_real_escape_string on any user input going into a query:

$query = "INSERT INTO table (fieldname1) VALUES ('" . mysql_real_escape_string($line1) . "');";