[SOLVED] Double quotes & single quotes

[New Coder]

Hello All,

 

I have a web page with four input boxes. If a user types in text with either single or double quotes the insert fails.

 

I have looked into solutions and there seems to be some disagreement on the best method.

 

Some people say turn on magic quotes while others say not to, or use addslashes and stripslashes and again some say not to.

 

What about an mssql version of mysql_real_escape_string and mysql_escape_string??

 

What is the correct method? How do I overcome?

 

Many Thanks

[trq]

Magic quotes are bad (imo) simply because they take the control away from you, the programmer.

 

It should always be left to the programmer to escape data appropriately. If you have mysql_real_escape_string, use it, otherwise, addslashes will normally suffice.

 

Stripslashes need really only be used if you want to display data that has had slashes added by magic quotes ($_POST and $_GET data). You don't need to strip slashes from data comming from the database because it shouldn't have any. Slashes escape special characters, the slashes themselves are not stored.

[New Coder]

So there isn't an equivalent MSSQL of mysql_real_escape_string?

 

I'm having trouble putting addslashes into my code:

 

<?php
  #CREATE VARIABLES
$issues =  $_POST['issues'] ; 
$compliments = $_POST['compliments'];

#connect to db						
$conn = @mssql_connect(  "", "", "" )
      			or die( "Err:conn");
#select db
$rs = @mssql_select_db( "", $conn)
		or die( "ERR:Db");

#create query
$sql = "insert into tbl_comment (issues, compliments)
	values( \"$issues\", \"$compliments\" ) ";

#exe query
$rs = mssql_query( $sql, $conn )
		or die( "Could not execute Query");

if($rs)
{ 
header( "Location:comments.php" ); exit();
}
mssql_close ( $conn );	
?>

 

I have tried

 

<?php
  #CREATE VARIABLES
$issues1 =  $_POST['issues'] ; 
$compliments1 = $_POST['compliments'];
             $issues =  addslashes($issues1); 
$compliments = addslashes($compliments1);


#connect to db						
$conn = @mssql_connect(  "", "", "" )
      			or die( "Err:conn");
#select db
$rs = @mssql_select_db( "", $conn)
		or die( "ERR:Db");

#create query
$sql = "insert into tbl_comment (issues, compliments)
	values( \"$issues\", \"$compliments\" ) ";

#exe query
$rs = mssql_query( $sql, $conn )
		or die( "Could not execute Query");

if($rs)
{ 
header( "Location:comments.php" ); exit();
}
mssql_close ( $conn );	
?>

 

and I get a blank page.

 

Where am I going wrong

[cooldude832]

I am not sure on an equivently, but basically what mysql_real_escape_string does is a find and replace for any of the special characters.  I think since its still a sql based langauge you can probably use the mysql_real_escape_string and be okay, but you might want to make your own function that replaces each special character with a \ in front of it using str_replace(); or some function like that (a eregi might be better)

[MadTechie]

try this

<?php
$compliments = (! get_magic_quotes_gpc ()) ? addslashes ($_POST['compliments ']) : $_POST['compliments '];
?>

 

EDIT: note use the same idea for all post's

[cooldude832]

or just do a foreach($_POST as $key => $value){$postvars[$key] = addslashes($value);}

it won't really screw you up doing it to all, but if you have a lot of checkboxes then it be pointless

[New Coder]

I've managed to get addslashes to work(ish!!) if a user inputs:   This wasn't great.  when it is dispalyed it is displayed as:  This wasn\'t great.   Which is obviously wrong...

 

also if a user inputs: He then answered "How can I help".

 

insert fails.

 

I need to allow users to input ' and ".

 

and addslashes doesn't seem to be working.

 

Thanks so far ppl

[MadTechie]

reversal

 

<?php
$compliments = ( get_magic_quotes_gpc ()) ? stripslashes ($compliments) : $compliments;
?>

 

EDIT: Ahh type'o

[New Coder]

but will that not just remove all \ ?

 

which if a user actual inputs: So I clicked the open\close button.

 

will then display as: So I clicked the openclose button.

 

It also doesn't handle " (speech marks)

 

 

 

[MadTechie]

Input (to the database)

 

<?php
$compliments = (! get_magic_quotes_gpc ()) ? addslashes ($_POST['compliments ']) : $_POST['compliments '];
?>

 

Output  (from the database)

<?php
$compliments = (get_magic_quotes_gpc ()) ? stripslashes ($compliments) : $compliments;
?>

 

 

Now, if the magic quotes are enabled then the don't add slashes on input, BUT on output

remove slashes if magic quotes are enabled,

 

Now if someone types

day\night

the input will be changed to

day\\night

thus the output filters the \

 

[trq]

I think since its still a sql based langauge you can probably use the mysql_real_escape_string and be okay

 

mysql_real_escape_string is actually mapped directly to an inbuilt mysql function. it will fail unless used with a mysql database.

 

I've managed to get addslashes to work(ish!!) if a user inputs:   This wasn't great.  when it is dispalyed it is displayed as:  This wasn\'t great.   Which is obviously wrong...

 

Is this when your displaying the data pulled from the database? Or just prior to an insert? Remember that the slashes don't actually get stored, they simply escape special chars while it gets entered.

[New Coder]

I've managed to get addslashes to work(ish!!) if a user inputs:   This wasn't great.  when it is dispalyed it is displayed as:  This wasn\'t great.   Which is obviously wrong...

 

Is this when your displaying the data pulled from the database? Or just prior to an insert? Remember that the slashes don't actually get stored, they simply escape special chars while it gets entered.

 

yes when pulled from the database.

[cooldude832]

mysql_real_escape_string is actually mapped directly to an inbuilt mysql function. it will fail unless used with a mysql database.

 

Didn't know this so if i say

mysql_real_escape_string($string);

and I don't have mysql set up it will die? I thought the mysql lib was pre installed automatically?

[MmmVomit]

From the manual.

 

Notes

Note: A MySQL connection is required before using mysql_real_escape_string() otherwise an error of level E_WARNING is generated, and FALSE is returned. If link_identifier isn't defined, the last MySQL connection is used.

 

A comment from the online manual at php.net

 

James at thetallfamily dot com

21-Jun-2007 12:36

MSSQL doesn't have a real_escape_string function like MYSQL does, which can lead to error when inserting or updating data that contains a ' (single quote).

 

To prevent this, replace all ' (single quotes) by TWO ' (single quotes) '' which SQL server will interpret as an escaped '.

Also you may want to remove any \' \" escape sequences that are translated from any FORM output into the PHP $_POST variables.

 

Hope this helps someone.

James

[New Coder]

right I have managed to allow single quotes/apostrohes to be entered but it still fails with double quote " speech marks.

 

Why is this??

[MmmVomit]

Here's something interesting.  Try it and see if it works.

 

http://www.daveshuck.com/blog/index.cfm/SQL

[MadTechie]

Post your code..

[New Coder]

Here is my code

 

<?php
  #GET VARIABLES
$issues =  $_POST['issues'] ; 
$compliments = $_POST['compliments'];

#connect to db						
$conn = @mssql_connect(  "", "", "" )
      			or die( "Err:conn");
#select db
$rs = @mssql_select_db( "", $conn)
		or die( "ERR:Db");

#create query
$sql = "insert into tbl_comment (issues, compliments)
	values( \"$issues\", \"$compliments\" ) ";

#exe query
$rs = mssql_query( $sql, $conn )
		or die( "Could not execute Query");

if($rs)
{ 
header( "Location:comments.php" ); exit();
}
mssql_close ( $conn );	
?>

 

 

Entering:-

 

I said "Hello World"

 

makes the insert fail

[MadTechie]

<?php
  #GET VARIABLES
//$issues =  $_POST['issues'] ; 
$compliments = (! get_magic_quotes_gpc ()) ? addslashes ($_POST['issues']) : $_POST['issues'];

//$compliments = $_POST['compliments'];
$compliments = (! get_magic_quotes_gpc ()) ? addslashes ($_POST['compliments ']) : $_POST['compliments '];


#connect to db						
$conn = @mssql_connect(  "", "", "" )
      			or die( "Err:conn");
#select db
$rs = @mssql_select_db( "", $conn)
		or die( "ERR:Db");

#create query
$sql = "insert into tbl_comment (issues, compliments)
	values( \"$issues\", \"$compliments\" ) ";

#exe query
$rs = mssql_query( $sql, $conn )
		or die( "Could not execute Query");

if($rs)
{ 
header( "Location:comments.php" ); exit();
}
mssql_close ( $conn );	
?>

 

[New Coder]

Thanks MadTechie but it is still failing.

 

 

[MadTechie]

Whats displayed..

 

NOTE add the line

 

<?php
  #GET VARIABLES
//$issues =  $_POST['issues'] ; 
$compliments = (! get_magic_quotes_gpc ()) ? addslashes ($_POST['issues']) : $_POST['issues'];

//$compliments = $_POST['compliments'];
$compliments = (! get_magic_quotes_gpc ()) ? addslashes ($_POST['compliments ']) : $_POST['compliments '];


#connect to db						
$conn = @mssql_connect(  "", "", "" )
      			or die( "Err:conn");
#select db
$rs = @mssql_select_db( "", $conn)
		or die( "ERR:Db");

#create query
$sql = "insert into tbl_comment (issues, compliments)
	values( \"$issues\", \"$compliments\" ) ";
echo $sql; //ADD THIS LINE
#exe query
$rs = mssql_query( $sql, $conn )
		or die( "Could not execute Query");

if($rs)
{ 
header( "Location:comments.php" ); exit();
}
mssql_close ( $conn );	
?>

 

[New Coder]

error returned:-

 

update tbl_comments set compliments = "\"hello\"", issues = "test" Could not execute Query

[mattal999]

im just having a stab but...

 

<?php
  #GET VARIABLES
//$issues =  $_POST['issues'] ; 
$compliments = (! get_magic_quotes_gpc ()) ? addslashes ($_POST['issues']) : $_POST['issues'];

//$compliments = $_POST['compliments'];
$compliments = (! get_magic_quotes_gpc ()) ? addslashes ($_POST['compliments ']) : $_POST['compliments '];


#connect to db						
$conn = @mssql_connect(  "", "", "" )
      			or die( "Err:conn");
#select db
$rs = @mssql_select_db( "", $conn)
		or die( "ERR:Db");

#create query
$sql = "insert into tbl_comment (issues, compliments)
	values( $issues, $compliments ) ";
echo $sql; //ADD THIS LINE
#exe query
$rs = mssql_query( $sql, $conn )
		or die( "Could not execute Query");

if($rs)
{ 
header( "Location:comments.php" ); exit();
}
mssql_close ( $conn );	
?>

[MadTechie]

OK well thats wrong.. the code above uses insert.. you have update!!!

in anycase..

	$sql = "insert into tbl_comment (issues, compliments)
	values( '$issues', '$compliments' ) ";

[kenrbnsn]

The following was posted in the mssql section on php.net:

MSSQL doesn't have a real_escape_string function like MYSQL does, which can lead to error when inserting or updating data that contains a ' (single quote).

 

To prevent this, replace all ' (single quotes) by TWO ' (single quotes) '' which SQL server will interpret as an escaped '.

Also you may want to remove any \' \" escape sequences that are translated from any FORM output into the PHP $_POST variables.

 

This may be your problem