Page 1 of 1

Do i insert / into database?

Posted: Sun Sep 27, 2009 12:07 pm
by merylvingien
Hi Fellas, i am just about to start building out a database for a new site and have been reading topics on here, trying to learn stuff as i go along (i am a bit thick so please bear with me)
I have noticed in a couple of topics that people have noted to NOT add slashes to a database.
The last site i worked on had a small database and when i tried to insert any ' into the database it wouldnt have it, so in the end i removed all of these from the data and just put up with it.
However this time the database is HUGE and i will need to have the ' in there.
So my question is: Do i insert data like this:

Code: Select all

INSERT INTO `tablename` (`row1`, `row2`, `row3`) VALUES (1, 'something', 'something with these[color=#FF0000]'[/color]', 'something more')
Or

Code: Select all

INSERT INTO `tablename` (`row1`, `row2`, `row3`) VALUES (1, 'something', 'something with these[color=#FF0000]/'[/color]', 'something more')
Loads more questions to come, but i think its best to tackle them one at a time.
Many thanks for any help.

Re: Do i insert / into database?

Posted: Sun Sep 27, 2009 4:29 pm
by merylvingien
No one? 8O

Re: Do i insert / into database?

Posted: Sun Sep 27, 2009 5:08 pm
by califdon
Just so you'll know, most of our users are turned off when somebody "bumps" their own post. If somebody is in a position to help you, they will do so without further comments, and sometimes they will deliberately skip your post if you have "bumped" it because it just clutters up the index, indeed it may look at first like somebody has already given you an answer. So for your own benefit, don't do it.

Now, to your question: yes, escape quote signs, but generally you will not do that by entering them manually, use either mysql_real_escape_string() or addslashes(). See http://www.w3schools.com/PHP/func_mysql ... string.asp

Re: Do i insert / into database?

Posted: Sun Sep 27, 2009 5:17 pm
by merylvingien
My humble appoligies, i wont do that again.

Many thanks for your answer, i am still a little confused as to why i wouldnt add the escape manually?
I am building the database from excel and using a notepad to upload the database when its complete.
If i dont escape the said characters in the notepad, then when i try and upload i just get an error. Thats why i was confused when a few people were saying that i shouldnt escape characters in the database.

I will read up the info in the link, once again many thanks.

Re: Do i insert / into database?

Posted: Sun Sep 27, 2009 6:15 pm
by califdon
For one thing, you will need to unescape them with stripslashes() whenever you use the data, unless your MySQL engine is set up for magic_quotes_gpc. Your process for loading data should not depend on a manual operation, wherever possible. You will miss some occurrences if you do it manually, and there will be no documentation of how the data was added. These are important matters in database work. So you're using Notepad to read the Excel file and editing the data? I hope you don't have very many rows of data! You might want to read about loading CSV files (which Excel can create) directly into a MySQL data base, then perhaps review and fix any anomalies that you find. It's a matter of how much data you have and how good (or bad) the data discipline is. If it's only a small number of rows, the manual approach is probably acceptable. I wouldn't want to do it with thousands or even hundreds of rows! Read the PHP manual about addslashes, stripslashes, mysql_real_escape_string and magic_quotes_gpc: http://us3.php.net/manual/en/function.addslashes.php. It is, admittedly, nonintuitive, but the purpose of all this is to allow certain characters to be encoded within stored strings that may be enclosed by those characters, themselves. If you try to send this SQL statement to MySQL, it will produce an error, because it sees the apostrophe in "O'Neil" as the end of the lastname field value, then some invalid characters:

Code: Select all

$sql = "SELECT * FROM myTable WHERE lastname='O'Neil'";
Most instructions discourage dependence on magic_quotes_gpc, because results may surprise you in some circumstances, particularly if you upgrade MySQL or try to use the script on a different server.