Getting confused with quotes (single and double) in databases

[Edward]

Hi,

 

I'm getting confused over quotes in databases. I'm entering the values manually, and some of them contact html. When I enter single and/or double quotes in my values, it seems to crash the database and stops mt editing it, but when I type in HTML entity equivilents, it stops the HTML displaying properly. Does anyone know what I should be doing?

 

An example value could be:

 

<div style="width: 100px;">I'm a website.</div>

 

Thank you!!

[PFMaBiSmAd]

Strings that contain characters with special meaning in sql must be escaped when they appear in data - http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html If you are inserting using php, use the mysql_real_escape_string() function.

 

Using HTML entities would prevent the browser from operating on them unless you converted them back to their non-entity form before you output them to the browser.

[Edward]

Hi,

 

Thanks for your comments. I understand what you are saying, but I am struggling to understand why we use mysql_real_escape_string() instead of htmlentities($var, ENT_QUOTES) - do they do the same thing?!

 

Anyway, my real problem is, in this particular case, I am firstly entering data by 'importing' a table, with the following code as a text:

 

CREATE TABLE `test` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(500) collate latin1_general_ci NOT NULL,
  `text` text collate latin1_general_ci NOT NULL,
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='A test table' AUTO_INCREMENT=2 ;
INSERT INTO `test` VALUES(1, 'Some Text', '<p style="font-weight: bold;">text<p>test</p><p>a single quote \' is here and a <a href="http://www.website.com/">link</a> is here</p>');

 

But this is causing a problem as I am unable to edit the table following import, presumably because there is a problem somewhere. If I remove the value with the quotes, it works fine. Do you know what I'm doing wrong?