MSSQL Quotation Marks problem.

[Arc]

I am using MSSQL and i am having users enter data. I run into a problem when Quotation marks or either kind are used.

I am using addslashes to try and take care of the problem but it isnt working.

FOr example: If i enter a string like this "asdasd" it adds it to the database as \\"asdasd\\". If i enter a string liek this 'asdasd' it adds it to the database like this ?asdasd?. If i use both kinds of quotations "asdasd" 'asdasd' i get an error saying "incorrect syntax" and the item doesnt get addded to the database at all.

I can manualy go into the DB and take out the \\ marks and it leaves the quotation marks just fine... i can also change the ? marks to ' marks and that works just fine. SO why is sit adding them like that? Is it a php problem or a Database problem or a code problem?

Also while i'm at it... what is the most chars varchar can hold in MSSQL? 255? or more? I have it set at 500 and didnt get any errors... I am using varchar to store the values.

Here is some of my code

   <?php
 If ($btnAddMenuItem){

$txtItemName = addslashes($txtItemName);
$txtItemDescription = addslashes($txtItemDescription);
$txtSecDes = addslashes($txtSecDes);
$txtThirdDes = addslashes($txtThirdDes);

If ($txtItemName=="" AND $txtItemDescription=="") {
Print "<Font Color=\"#0000FF\" size=4><b>[AN ERROR HAS OCCURED]</font></b><br>";
Print "You must enter either an Item Name or Item Description. <br>";

 } else {

Include("dbconnect.php");
$SQL = "INSERT INTO tblRegularMenu (MenuID, Category, ItemName, ItemDescription,
 Price, SecDes, SecPrice,ThirdDes, ThirdPrice) VALUES ('$txtMenu', '$txtCategory', '$txtItemName',
'$txtItemDescription','$txtPrice','$txtSecDes','$txtSecPrice',
'$txtThirdDes','$txtThirdPrice')";
	$result = mssql_query($SQL);
	Print "<META HTTP-EQUIV=\"refresh\" CONTENT=\"0; URL=addmenuitemsuccess.php\">";
	}
}
?>

Thanks!😃

[ahundiak]

Couple of things going on here. First, the browser will actually add some slashes to your posted data. You need to strip these away. I use this function which also does a bit of length validation and strips out html tags.

function http_get_post_str($name,$len=0)
{
  $str = strip_tags(stripslashes($_POST[$name]));
  if ($str == null) return null;
  if ($len && (strlen($str) > $len)) return null;
  return $str;
}
$txtItemName = http_get_post_str('txtItemName'];

Next, when it comes time for inserting into the database, it's really only the single quotes that cause problems. Double quotes are fine. You need to change each single quote into two single quotes. I use this

function str_insert($str)
{
    return str_replace("'",'\'\'',$str);
}  

$txtItemName = str_insert($txtItemName);
$sql = "Insert into whatever values('$txtItemName')";

For some reason the PHP tag is knocking out my back slashes. The second argument is
Single Quote Backslash Single Quote Backslash Single Quote Single Quote

To be honest, I don't remember why I did it that way instead of using "''".

[Arc]

I've used the exact method as above with MySQL DB and never had any problem with any kind of quotation marks. Single or regular.

Changing 1 single quote to 2 single quotes isnt an option. I dont want O'dool's to become O''Dool''s.

[ahundiak]

A beer does sound good.

I didn't notice the MSSQL part of you post. I don't have access to such a server so I don't know if the double single quotes will work or not.

However, for mysql
mysql_query("Insert into osso_person (person_id,person_cname) values(22,'O''dull')");

Will in fact insert O'dull into the database. You can go in with your admin tool and verify this. The two double single quotes get combined into one single quote.

Just for grins, you might want to give it a try under MSSQL.

[Arc]

if i try to add double single quotes i get this error

Warning: Sybase error: Line 1: Incorrect syntax near '\'. (severity 15) in /home/httpd/html/admin/menu/addmenuitemreg.php on line 32

I'm using addslashes... If i dont use addslashes i get a different error... no idea what the deal is.

[ahundiak]

Strange. I really thought that using the double single quotes was more or less a sql standard. Maybe there is a MSSQL config option somewhere.

I checked the sybase site and it seems to agree.
http://manuals.sybase.com/onlinebooks/group-as/srg1100e/sqlref/@ebt-link;pt=39127?target=%25N%14_9370_START_RESTART_N%25

The full link did not paste in but look under
Chapter 2
Standards and Compliance
Character Datatypes
Entering Character Data

Did you try a simple insert with just a hardcoded string?

[Arc]

Entering Character Data
Character strings must be enclosed in single or double quotes. If you have set quoted_identifier on, use single quotes for character strings or SQL Server treats them as identifiers.

Strings that include the double-quote character should be surrounded by single quotes. Strings that include the single-quote character should be surrounded by double quotes. For example:

'George said, "There must be a better way."' "Isn't there a better way?"
An alternative is to enter two quotation marks for each quotation mark you want to include in the string. For example:

"George said, ""There must be a better way."" 'Isn''t there a better way?'
To continue a character string onto the next line of your screen, enter a backslash () before going to the next line.

Well this explains a little bit. But the examples do not explain if there are 2 single quotes right next to each other or 2 double quotes right next to each other or if there are single AND double quotes in 1 string.

Plus when you are using variables as your data you have no idea what the user has entered, and trying to write a function that fixes every possible combonation of quotation marks would be very hard. Surely it's not that complex.. THere must be a simpler answer..

[ahundiak]

You might be making it a bit harder than it really is.

While sql allows you to use either single or double quotes when building the insert statement, you only need to use one.

So, assuming you stick with single quotes then you can forget about any problems from double quotes. All you need to do is to run the str_replace function on whatever the user enters and basically double up all the single quotes. Even if the user enters

My '' string
The str_replace will yield
My '''' string
Which in turn will be translated back into
My '' string
when inserted into the database.

The key point is to cleanly separate the html conversion stuff from the database stuff. You just need to be aware that browsers will insert those backslashes when the user puts in 's or "s. Hence the need to stripe them out before posting.

And, you need to put them back in again using htmlspecialchars after you have retrieved the values from the database and you are getting ready to send them back to the browser.

[Arc]

Ok so i dont need to use addslashes? I dont understand striping slashes before adding to the DB.. aren't you supposed to do that when pulling From the DB?

I am using single quotes to add to the DB.. so all double quotes will be fine. So say i had this string.

$String ='I went to the store and bought some O'dool's beer. When i got to the counter the guy said "Are you 21?". I said "what's it to yah?".'

So you're saying i should do a $String=str_replace("singlequote","singlequotesinglequote",$String); ?

What about addslashes? Where does that come into play?

Thanks for the help!😃

[ahundiak]

Oh what a tangled web we have.

Lookup addslashes() in the php manual and read through the comments.
http://www.php.net/manual/en/function.addslashes.php

You will find a big discussion on this whole slash thing.

The bottom line is that you don't need to use addslashes for mysql. It turns out that mysql supports a propierity extension to it's sql processor which does support slashes. That's why your existing code works. But, MSSQL does not.

I have used sql interfaces for many years. When I started in php I just automatically doubled the quotes and never worried about this whole slash problem as far as the database was concerned.

Next, follow the majic_quotes_gpc link. Basically, the php developers decided to make things 'easier' for regular developers by automatically slashing data from GET/POST/COOKIES/ENV etc. That's why you end up getting slashes when pulling data from say $_POST. And that's why I use stripslashes to get rid of them. You should actually check a global variable before using stripslashes() since it's possible to turn off this majic quoting. The manual has examples.

When selecting data from the database, no automatic quoting or slashing is done. Yeah for small favors. As far as I know, you don't have to do anything special.

The final piece of the puzzle comes in when sending a string to your html form. Certain characters (besides just quotes and double quotes) have special meaning in html and must be turned into entities. The htmlspecialchars() function accomplishes this.

So, to repeat the bottom line, I don't think you need to use addslashes at all when interfacing to a database.

I don't pretend to have written dozens of php applications on multiple platforms. So there may be other cases where you do need addslashes(). But I have implemented a few and so far at least, whatever the user types into one of my forms ends up getting stored correctly in mysql.

[Arc]

OK.. i'll give it a shot and see what happens.. thanks!

[Arc]

Ok GREAT! I got it to add to the DB using this.

$txtItemName = stripslashes(str_replace("'","''",$txtItemName));

I'm not using addslashes at all which is wierd for me...but it works 🙂

Now i am having a problem pulling data FROM the DB🙁

If i have an item in the DB with single quotes in it it wont pull out. I am using single quotes in the SQL statement..

$Sql="SELECT OnTap FROM tblIceColdBeer WHERE MenuID LIKE '$MenuID' AND BeerName LIKE '$Item'";

If $Item = O'Doul's i get a SYbase error.

Warning: Sybase error: Line 1: Incorrect syntax near 'Doul'. (severity 15) in /home/httpd/html/getpowered/wildhorse/admin/menu/editmenuitemicebeer2.php on line 13.

I can't replace the single quotes with double single quotes in $Item becasue then it won't find it in the DB...so how do i get around this?

Thanks!
😃

[ahundiak]

It's the same issue. Go ahead and double the single quotes in $item.

This works:
"Select person_id from osso_person where person_cname = 'O''dull'"

Once $item makes it to the database server then the double single quotes are converted back to single single quotes (there has to some fancy term for a double single quote?). And it should match without a problem.

And I think you really want to use stripslashes before the str_replace. You want to have a $php variable containing exactly the string that you want stored in the database then run str_replace on it as you insert it.

[Arc]

You're right it does work🙂

1 last problem i think then i'll have this fixed.

When displaying an item that has regular quotes " How do i do it? I am trying to use the same technique but it's not working.

<?php $Item=str_replace('"','""',$Item); ?>
                <?php Print "<input type=\"text\" value=\"$Item\" name=\"txtItemName\" size=30>"; ?>

Trying to replace 1 " with 2 "". I dont know if it's becasue of the escape slashes in the input value or what..but the item doesnt show up.

[ahundiak]

Send it through htmlspecialchars().

$str = 'I have a " in me';
echo htmlspecialchars($str);

You might want to look at the manual for htmlspecialchars(). There are a couple of options. I currently use the default but I think I really should be using one of the options.

Keep the requirements for sql and for html separate in your mind. The whole double single quote thing is not applicable to html.

[Arc]

Thank you very much man...you're a life saver. I think everything is working now 😃