Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Single Quotes & MySQL


neerajpoudyal

Recommended Posts

The mysql version at my hosting (they're not going to change it) is not intelligent enough to distinguish single quotes in description fields (eg: it throws errors in products_description). Addslashes does the job of inserting without error, but then the slashes go into database. In small applications I don't mind removing that with stripslashes, but OSCOMMERCE is too huge and I don't want to change that for every select statement within the application. Its even annoying when this happens to easypopulate.

 

Can you suggest something. Is there a mysql configuration setting where I can ask it to ignore single quotes within a field? Better yet can you explain the working of single quotes?

Link to comment
Share on other sites

The mysql version at my hosting (they're not going to change it) is not intelligent enough to distinguish single quotes in description fields (eg: it throws errors in products_description). Addslashes does the job of inserting without error, but then the slashes go into database. In small applications I don't mind removing that with stripslashes, but OSCOMMERCE is too huge and I don't want to change that for every select statement within the application. Its even annoying when this happens to easypopulate.

 

Can you suggest something. Is there a mysql configuration  setting where I can ask it to ignore single quotes within a field? Better yet can you explain the working of single quotes?

 

NO, all version of MySQL handle this in the same way. And it needs the single quotes to be escaped when stored in the database. Just the way it is, not a bug or anything else wrong.

 

The opposite of addslashes() is stripslashes() when you retrieve your data apply this.

Link to comment
Share on other sites

Better yet can you explain the working of single quotes?

 

 

Reason for this happening

 

look at this code

 

$MyString = "Mike\'s string";

 

$InsertQuery = "insert into mytable values ('".$MyString."')";

 

which would turn into

 

insert into mytable values ('Mike\'s string')

 

strings are enclosed by single quotes, so without the escape it would do this

 

insert into mytable values ('Mike''s string')

 

it thinks you are trying to insert the string Mike but all the stuff after that 's string') is an error as far as MySQL is concerned as it is not valid SQL code

Link to comment
Share on other sites

NO, all version of MySQL handle this in the same way.  And it needs the single quotes to be escaped when stored in the database.  Just the way it is, not a bug or anything else wrong.

 

Well the windows phpDev I am using has a mySQL that does it. I am using the same easy populate in two different versions (linux hosting), one doesnot need addslashes/stripslashes to work, other does.

 

I was actually inquiring if you can configure mySQL to be "more intelligent" about the single quotes.

 

The opposite of addslashes() is stripslashes() when you retrieve your data apply this.

 

I am aware of that. :D Thanks anyways for helping me out.

Link to comment
Share on other sites

Well the windows phpDev I am using has a mySQL that does it.  I am using the same easy populate in two different versions (linux hosting), one doesnot need addslashes/stripslashes to work, other does.

 

I was actually inquiring if you can configure mySQL to be "more intelligent" about the single quotes.

I am aware of that. :D  Thanks anyways for helping me out.

 

 

Magic quotes may be turned on in your php.ini file on your other host, but this has bugger all to do with MySQL. Maybe try turning that on in your other hosts php.ini file and see what happens.

 

I know what you were asking and I said no it cant be, then went on to explain why it does what it does (which you also asked) :rolleyes:

Link to comment
Share on other sites

One thing to keep in mind is that in SQL, single quotes are escaped by another single quote, and not by a slash.

 

INSERT into table SELECT "This''s the way you escape a single quote"

 

That might get a little tricky when you consider that in php, in a string, the single quote needs to be escaped by a backslash.

 

 

$sql='INSERT into table SELECT "This\'\'s the way you escape a single quote"'

 

Or, something like that.

-------------------------------------------------------------------------------------------------------------------------

NOTE: As of Oct 2006, I'm not as active in this forum as I used to be, but I still work with osC quite a bit.

If you have a question about any of my posts here, your best bet is to contact me though either Email or PM in my profile, and I'll be happy to help.

Link to comment
Share on other sites

One thing to keep in mind is that in SQL, single quotes are escaped by another single quote, and not by a slash.

 

INSERT into table SELECT "This''s the way you escape a single quote"

 

That might get a little tricky when you consider that in php, in a string, the single quote needs to be escaped by a backslash.

$sql='INSERT into table SELECT "This\'\'s the way you escape a single quote"'

 

Or, something like that.

 

Thats only because you enclose the entire string ($sql) in single quotes, not the case of you use double quotes.

 

MySQL is happy with a slash to escape the single quote

 

$sql="INSERT into table (myfield) values ('This\'s the way you escape a single quote')"

 

but I always enclose my strings with double quotes and string values I'm passing to MySQL in single quotes (hence the need to use slash).

 

Yes if you enclose ALL your strings in PHP with single quotes then you have to start doing all this ''' stuff, which makes it a nightmare to read in 2 months time when you've got a bug :blush:

Link to comment
Share on other sites

Hey Mike, that doesn't look like it'll work to me.

 

My thought is that the php doesn't interpret the backslash literally, therefore it never makes it to MySQL. So MySQL receives the SQL that looks like this.

 

INSERT into table (myfield) values ('This's the way you escape a single quote')

 

Which most certainly will fail.

 

 

 

Thats only because you enclose the entire string ($sql) in single quotes, not the case of you use double quotes.

 

MySQL is happy with a slash to escape the single quote

 

$sql="INSERT into table (myfield) values ('This\'s the way you escape a single quote')"

 

but I always enclose my strings with double quotes and string values I'm passing to MySQL in single quotes (hence the need to use slash).

 

Yes if you enclose ALL your strings in PHP with single quotes then you have to start doing all this ''' stuff, which makes it a nightmare to read in 2 months time when you've got a bug  :blush:

-------------------------------------------------------------------------------------------------------------------------

NOTE: As of Oct 2006, I'm not as active in this forum as I used to be, but I still work with osC quite a bit.

If you have a question about any of my posts here, your best bet is to contact me though either Email or PM in my profile, and I'll be happy to help.

Link to comment
Share on other sites

You're right it does see it as

 

INSERT into table (myfield) values ('This's the way you escape a single quote')

 

but it doesn't fail, it stores

 

This's the way you escape a single quote

 

in the database

Link to comment
Share on other sites

Sorry to keep harping on this, Mike, but I'm a Database Analyst/Programmer by trade, so this is the kind of thing I work with 8 hours per day.

 

It looks to me that you are mistaken. The following is not correct, and will result in an error.

 

INSERT into table (test1) values ('This's the way you escape a single quote')

 

This is the error you will receive.

 

There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem

ERROR: Unclosed quote @ 75
STR: '
SQL: INSERT into table (test1) values ('This's the way you escape a single quote')

 

The way I would enter this into the database is like this.

 

INSERT into test1 SELECT ('This''s the way you escape a single quote')

 

This will store the following value...

This's the way you escape a single quote

 

 

In MySQL, you can also use a double quote instead

INSERT into test1 SELECT ("This's the way you escape a single quote")

 

However, this is probalby more complicated if you use double quotes for the php string, and this syntax does not work on most DBMSs, such as MS SQL Server and Oracle.

-------------------------------------------------------------------------------------------------------------------------

NOTE: As of Oct 2006, I'm not as active in this forum as I used to be, but I still work with osC quite a bit.

If you have a question about any of my posts here, your best bet is to contact me though either Email or PM in my profile, and I'll be happy to help.

Link to comment
Share on other sites

Sorry to keep harping on this, Mike, but I'm a Database Analyst/Programmer by trade, so this is the kind of thing I work with 8 hours per day.

 

No problem, happens that I am also. Mostly Oracle.

 

In PHP

mysql_connect('localhost','user','pass');
mysql_select_db('my_db');

$SQL = "insert into my_table (my_field) values ('This\'s a string2')";

mysql_query($SQL);

$SQL = "select * from my_table";
$Result = mysql_query($SQL);

while($Record = mysql_fetch_array($Result)){
print "The record is: ".$Record['my_field']."<br />";
} // while

 

obviously if you dont include the slash (\') then it looks like the end of the value being inserted is the middle ' and then s a string2') is obviously not valid SQL syntax for an insert statement hence causing the error. But that is why the slash is in there so that it treats the ' as a literal character.

 

Try that bit code for yourself and tell me it doesnt insert

 

This's a string2 into the database field. It will produce exactly the same result as using This''s a string2, but to me This\'s a string2 is more readable particularly when you have double quotes, single quotes all over the place. Thats just a preference.

Link to comment
Share on other sites

I stand corrected. That certainly does work.

 

MySQL treats the \ as an escape character as well. But what I still don't understand is how it see's the \ in the first place.

 

In php, the \ is not a literal value. So MySQL should never even see the \, and therefore the ' wouldn't be escaped. I guess that's the assumption I was making. Obviously, that's not correct, as you've proven, but it doesn't make alot of sense to me.

 

Is this the case in all escape type situations?

-------------------------------------------------------------------------------------------------------------------------

NOTE: As of Oct 2006, I'm not as active in this forum as I used to be, but I still work with osC quite a bit.

If you have a question about any of my posts here, your best bet is to contact me though either Email or PM in my profile, and I'll be happy to help.

Link to comment
Share on other sites

No problem, happens that I am also.  Mostly Oracle.

 

In PHP

mysql_connect('localhost','user','pass');
mysql_select_db('my_db');

$SQL = "insert into my_table (my_field) values ('This\'s a string2')";

mysql_query($SQL);

$SQL = "select * from my_table";
$Result = mysql_query($SQL);

while($Record = mysql_fetch_array($Result)){
print "The record is: ".$Record['my_field']."<br />";
} // while

 

obviously if you dont include the slash (\') then it looks like the end of the value being inserted is the middle ' and then s a string2') is obviously not valid SQL syntax for an insert statement hence causing the error.  But that is why the slash is in there so that it treats the ' as a literal character.

 

Try that bit code for yourself and tell me it doesnt insert

 

This's a string2 into the database field. 

 

 

 

Mike what bit of code are you talking about using instead of

 

$SQL = "insert into my_table (my_field) values ('This\'s a string2')"; ?

 

 

 

A different note, I think that's why in some cases addslashes($var) doesn't work but addslashes(addslashes($var)) does.

Link to comment
Share on other sites

Sorry Neeraj we went a bit off topic there, just discussing different ways of escaping characters.

 

mysql_connect('localhost','user','pass');
mysql_select_db('db');

$string = addslashes("this's a string");
$SQL = "insert into my_table (my_field) values ('$string')";

mysql_query($SQL);

$SQL = "select * from my_table";
$Result = mysql_query($SQL);

while($Record = mysql_fetch_array($Result)){
prprt "The record is: ".$Record['my_field']."<br />";
} // while

 

In that bit code although I used addslashes, but it is only added for the purposes of getting the SQL statement to run without error. It doesn't actually store the slash in the database field.

 

In that bit code

 

this's a string

 

is what ends up in the DB.

 

If you do as you say addslashes(addslashes($string)), it does add a slash into the DB, what PHP actually passes to the SQL statement is

 

The string is this\\\'s a string

 

so the first slash escapes the slash and the third slash escapes the quote, so in the DB you get

 

The record is: this\'s a string

 

Confused :'( , yep.

 

Anyway, apparently we should use the function mysql_escape_string() or mysql_real_escape_string() (PHP >= 4.0.3)

Link to comment
Share on other sites

Howdy,

You folks seem to be up on mysql, perhaps you can point me in the right direction.

 

After adding a contrib (works) tried to add a new product and came up with this:

 

1064 - You have an error in your SQL syntax near '0, products_date_available, products_weight, products_status, products_tax_class' at line 1

 

insert into products (products_quantity, products_model, products_make_an_offer, products_price, 0, products_date_available, products_weight, products_status, products_tax_class_id, manufacturers_id, products_date_added) values ('', 'PATEST', '', '', '1999.99', null, '39', '1', '1', '', now())

 

Should I be looking at the moded file (didn't see anything out of line)

or the sql used to add to the data base (did, but no clue as to what I should be looking for)

Thanks

Link to comment
Share on other sites

insert into products (products_quantity, products_model, products_make_an_offer, products_price, 0, products_date_available, products_weight, products_status, products_tax_class_id, manufacturers_id, products_date_added) values ('', 'PATEST', '', '', '1999.99', null, '39', '1', '1', '', now())

 

Columns in MySQL should not be named after numbers, mainly due to the fact it is not very descriptive (but is allowed)

 

insert into products (products_quantity, products_model, products_make_an_offer, products_price, 0, products_date_available, products_weight, products_status, products_tax_class_id, manufacturers_id, products_date_added) values ('', 'PATEST', '', '', '1999.99', null, '39', '1', '1', '', now())

 

There are also 11 columns in the field list but only 10 in the values list. Did the contribution require you to create a column 0 on your products table.

 

However even removing it I'm not sure things are going to work, is '1999.99' the product price? If you count where this appears in the values list it's fifth, but product_price is forth in the list of columns.

 

Something not quite right here, I think you should look at the contribution documentation again.

Link to comment
Share on other sites

Deleted all from the db and as you said it still does not work, reloaded the orig file and it works , so now to find my error.

Thank you.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...