Page 1 of 1

inserting single quotes

Posted: Mon Dec 13, 2004 5:04 pm
by C_Calav
hi guys,

when i am using this insert script, i get an error when i try and insert single quotes around words.

double quotes insert fine. why is this?

my feild type is ' text ' in mySQL

the parts of my insert script that involve the feild are
$P_Desc = stripslashes($_POST["P_Desc"]);

Code: Select all

$sql  = "INSERT INTO planes (P_Stock, P_Name, P_Cat, P_Scale, P_Length, P_Span, P_CostPrice, P_Price, P_Desc) VALUES ('$P_Stock','$P_Name','$P_Cat','$P_Scale','$P_Length','$P_Span','$P_CostPrice','$P_Price','$P_Desc')";
               $result = mysql_query($sql, $db) or die ("Insert Plane :: Execution failed.");

this is a error message when i put single quotes in this feild
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 'test'' WHERE P_Stock='12334'' at line 1
Couldn't execute query: UPDATE planes SET P_Name='Test',P_Cat='0',P_Scale='54',P_Length='54',P_Span='54',P_CostPrice='54',P_Price='54',P_Desc='This is a "test" and another 'test'' WHERE P_Stock='12334'
if anyone could help me so i can insert single quotes that would be awsome!

Posted: Mon Dec 13, 2004 5:32 pm
by timvw
instead of [php_man]stripslashes[/php_man] do a [php_man]mysql_real_escape_string[/php_man]

Posted: Mon Dec 13, 2004 5:40 pm
by C_Calav
thanx for that tim!

is this right now?

Code: Select all

$P_Price = mysql_real_escape_string($_POSTї"P_Price"]);
i got
Insert Plane :: Execution failed.


which is this bit of code:

Code: Select all

$sql  = "INSERT INTO planes (P_Stock, P_Name, P_Cat, P_Scale, P_Length, P_Span, P_CostPrice, P_Price, P_Desc) VALUES ('$P_Stock','$P_Name','$P_Cat','$P_Scale','$P_Length','$P_Span','$P_CostPrice','$P_Price','$P_Desc')";
               $result = mysql_query($sql, $db) or die ("Insert Plane :: Execution failed.");

Posted: Mon Dec 13, 2004 5:53 pm
by John Cartwright
You can also try [php_man]addslashes[/php_man] when inserting into the database, the strip the slashes when displaying

Posted: Mon Dec 13, 2004 5:56 pm
by C_Calav
thanx Phenom

code is like this now and get the same error asd above

Code: Select all

$P_Price = addslashes($_POSTї"P_Price"]);

Posted: Mon Dec 13, 2004 6:14 pm
by John Cartwright
change

Code: Select all

or die ("Insert Plane :: Execution failed.");
to

Code: Select all

or die ("Insert Plane :: Execution failed :: ".mysql_error());
It will give you a clue to what is wrong. Try echoing out the result and making sure the quotes are being accounted for. Another thing I might suggest is try adding into your database a price without a quote in it, and see if the query becomes valid.

Posted: Mon Dec 13, 2004 6:20 pm
by C_Calav
thanx Phenom,

with addslashes in place i get this error.
Insert Plane :: Execution failed :: 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 '(P_Stock, P_Name, P_Cat, P_Scale, P_Length, P_Span, P_CostPrice

also if it helps here are the rest of the feilds:

Code: Select all

$P_Stock = stripslashes($_POSTї"P_Stock"]);
$P_Name = stripslashes($_POSTї"P_Name"]);
 $P_Cat = stripslashes($_POSTї"P_Cat"]);
$P_Scale = stripslashes($_POSTї"P_Scale"]);
$P_Length = stripslashes($_POSTї"P_Length"]);	
$P_Span = stripslashes($_POSTї"P_Span"]);
$P_CostPrice = stripslashes($_POSTї"P_CostPrice"]);
$P_Price = addslashes($_POSTї"P_Price"]);
$P_Desc = stripslashes($_POSTї"P_Desc"]);
thanx

Posted: Mon Dec 13, 2004 6:25 pm
by John Cartwright
since your variable names and colum names are identical, you dont neccesarily require you to name the columns, you can do something like

Code: Select all

$sql  = "INSERT INTO `planes` VALUES ('$P_Stock','$P_Name','$P_Cat','$P_Scale','$P_Length','$P_Span','$P_CostPrice','$P_Price','$P_Desc')"; 
$result = mysql_query($sql, $db) or die ("Insert Plane :: Execution failed :: ".mysql_error());
Try removing all the stripslashes, this should be done when you are fetching the information. Remember, you should never trust when they user can input in terms of input. (sql injection)

Posted: Mon Dec 13, 2004 6:44 pm
by C_Calav
ok done the changes. still get a error
Insert Plane :: Execution failed :: 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 ''planes' VALUES ('12345678','Testing','Helicopters','54','54','
php now looks like this

Code: Select all

$P_Stock = addslashes($_POSTї"P_Stock"]);
$P_Name = addslashes($_POSTї"P_Name"]);
 $P_Cat = addslashes($_POSTї"P_Cat"]);
$P_Scale = addslashes($_POSTї"P_Scale"]);
$P_Length = addslashes($_POSTї"P_Length"]);	
$P_Span = addslashes($_POSTї"P_Span"]);
$P_CostPrice = addslashes($_POSTї"P_CostPrice"]);
$P_Price = addslashes($_POSTї"P_Price"]);
$P_Desc = addslashes($_POSTї"P_Desc"]);

Code: Select all

$sql  = "INSERT INTO 'planes' VALUES ('$P_Stock','$P_Name','$P_Cat','$P_Scale','$P_Length','$P_Span','$P_CostPrice','$P_Price','$P_Desc')";
			$result = mysql_query($sql, $db) or die ("Insert Plane :: Execution failed :: ".mysql_error());

also, since i have changed the code, it now comes up with that error even if i dont use single quotes in the desc text area

Posted: Mon Dec 13, 2004 7:46 pm
by timvw
if you don't provide [php_man]mysql_real_escape_string[/php_man] a mysql connection resource it will return an empty string...

Code: Select all

$link = mysql_connect(.....);

$foo = $_POST['foo'];
if (get_magic_quotes_gpc())
{
   // fix screwup
    $foo = stripslashes($_POST['foo']);
}

$clean = mysql_real_escape_string($foo, $link);
or you could use [php_man]mysql_escape_string[/php_man] which does not require a link to the mysql resource.....

Posted: Mon Dec 13, 2004 7:51 pm
by C_Calav
thanx tim,

can you please explain that code a little more im not sure exactly what to do :?

i get the magic quotes bit, what does " mysql_real_escape_string " do?

and how can i do this for multipule variables?

and how would i insert them to the DB?

thanx very much!

Posted: Mon Dec 13, 2004 8:41 pm
by timvw
you can read it all at [php_man]mysql_real_escape_string[/php_man]

basically, it escapes/adds slashes to the following characters: NULL, \x00, \n, \r, \, ', " and \x1a


fe: assume someone has posted foo

Code: Select all

error_reporting(E_ALL);

// connect to database
$db = mysql_connect('localhost', 'user', 'pass') or die(mysql_error());
mysql_select_db('database', $db) or die(mysql_error());

// handle data if it was posted
if (array_key_exists('foo', $_POST))
{

   // grab foo and make sure it is in it's original state
   if (get_magic_quotes_gpc())
   {
       $foo = stripslashes($_POST['foo']);
    }
    else
    {
        $foo = $_POST['foo'];
     }

    // now make it ready to be inserted in our database
    $foo = mysql_real_escape_string($foo, $db);

     // now build the query and perform it
    $query = "UPDATE table SET foo='$foo'";
     $result = mysql_query($query) or die(mysql_error());
}


if you retrieve data from the table, you should not to forget stripslashes on all the columns.......

Code: Select all

...
$query = "SELECT * FROM table";
$result = mysql_query($query, $db);
while ($row = mysql_fetch_assoc($result))
{
    foreach($row as $key => $value)
     {
          $row[$key] = stripslashes($value);
     }
       
      // at this point we have the original values in $row

}

Posted: Mon Dec 13, 2004 8:53 pm
by C_Calav
thanx very much tim thats easy to follow now!

will try that now,

i have to questions though, should it be "insert" rather than "update" or does it not matter? i mean, this code can be used for update and insert?

Code: Select all

$query = "UPDATE table SET foo='$foo'";
how can i do this for multipule variables is this ok?

and how would i put multiple vars in here

if (array_key_exists('foo', $_POST)) ??

Code: Select all

<?php

if (array_key_exists('foo', $_POST))
{  
 // grab foo and make sure it is in it's original state   
if (get_magic_quotes_gpc())
{      
 $foo = stripslashes($_POST['foo']);   
 $name = stripslashes($_POST['name']);   

 }    

else    
{        
$foo = $_POST['foo'];     
$name = $_POST['name'];     
}    

// now make it ready to be inserted in our database    
$foo = mysql_real_escape_string($foo, $db);    
$name = mysql_real_escape_string($foo, $db);    


 // now build the query and perform it    
$query = "UPDATE table SET foo='$foo', name='$name' ";

?>
please let me know if this is on the right track!

Posted: Mon Dec 13, 2004 9:42 pm
by timvw
you are on the right track ;) ;)

Code: Select all

function stripslashes_deep($value)
{
   $value = is_array($value) ?
               array_map('stripslashes_deep', $value) :
               stripslashes($value);

   return $value;
}
if (get_magic_quotes_gpc())   $_POST = stripslashes_deep($_POST);