inserting single quotes

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
C_Calav
Forum Contributor
Posts: 395
Joined: Wed Jun 02, 2004 10:55 pm
Location: New Zealand

inserting single quotes

Post 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!
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

instead of [php_man]stripslashes[/php_man] do a [php_man]mysql_real_escape_string[/php_man]
User avatar
C_Calav
Forum Contributor
Posts: 395
Joined: Wed Jun 02, 2004 10:55 pm
Location: New Zealand

Post 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.");
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

You can also try [php_man]addslashes[/php_man] when inserting into the database, the strip the slashes when displaying
User avatar
C_Calav
Forum Contributor
Posts: 395
Joined: Wed Jun 02, 2004 10:55 pm
Location: New Zealand

Post 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"]);
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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.
User avatar
C_Calav
Forum Contributor
Posts: 395
Joined: Wed Jun 02, 2004 10:55 pm
Location: New Zealand

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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)
User avatar
C_Calav
Forum Contributor
Posts: 395
Joined: Wed Jun 02, 2004 10:55 pm
Location: New Zealand

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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.....
User avatar
C_Calav
Forum Contributor
Posts: 395
Joined: Wed Jun 02, 2004 10:55 pm
Location: New Zealand

Post 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!
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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

}
User avatar
C_Calav
Forum Contributor
Posts: 395
Joined: Wed Jun 02, 2004 10:55 pm
Location: New Zealand

Post 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!
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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);
Post Reply