Solved

MySQL Double Entry

Posted on 2012-03-29
2
327 Views
Last Modified: 2012-04-03
I need a way to keep entrys into a database from being doubled. The issue is that on something like

%7B%7D that is the same as {} in html. So if one is entered in with the percentages and one is entered in normal, the data base thinks they are two different items even though they could be the exact same thing.

Now when I insert the new item into the data base the value ='s mysql_real_escape_string(htmlentities(item)) is how I do it now. This is wrong im guessing cause it could go with either of the above examples and puts them in as given.

So my question here is kinda two parted.

 1) how do I get it to insert the info one way or the other but never both being allowed. So {} will always show %7B%7D when inserted or visa versa.

and

2) How do I take an existing database and sort threw it to make sure they are all the same as 1 codes the new ones in, and take out any possible dupilications due to the difference?
0
Comment
Question by:mimoser
2 Comments
 
LVL 76

Assisted Solution

by:arnold
arnold earned 250 total points
Comment Utility
Instead of only item, you need htmlenities (item,null,"utf8")
http://stackoverflow.com/questions/405684/php-mysql-with-encoding-problems
Not sure what are you storing so it is hard to now what you are writing into the database.
http://www.php.net/manual/en/function.html-entity-decode.php
http://www.php.net/manual/en/function.htmlentities.php
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 250 total points
Comment Utility
Wow, this is like comparing apples and (not oranges, but) fish.  There are a lot of "interesting" parts to the question.  Let's step through the data flow.

Getting it from client input to data base storage
When external data arrives in the PHP script, perhaps from client input via a form or from a web service, etc., it needs to be "escaped" so the data can be put into a data base.  For this purpose, you use mysql_real_escape_string().  You can read the man page to learn what it does.  Escaped data is safe to put into the data base.  The escape characters are almost always backslashes.  They are removed by MySQL when the data is written.  If this entire process is done correctly you wind up with exactly the same information in the data base.  Your client typed O'Reilly and your data base contains O'Reilly.  That's the expected process. The most common error condition arises when magic quotes has escaped the data and the script also calls mysql_real_escape_string().  Then you get things that look like O\'Reilly in the data base.  This article explains a little bit of what can go wrong with magic quotes.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_6630-Magic-Quotes-a-bad-idea-from-day-one.html

Getting it from data base storage to client output
Now that we have received external data and stored it, it's time to regurgitate it for browser output.  We want to be careful that we do not send something "toxic" to one of our clients.  Imagine if one bad client put a dangerous piece of JavaScript into our data base.  The last thing we want to do is cause that JavaScript to run on all the other client computers!  This is where the entity functions come into play.  To quote the online man pages, "... useful in preventing user-supplied text from containing HTML markup, such as in a message board or guest book application"
http://php.net/manual/en/function.htmlspecialchars.php
http://php.net/manual/en/function.htmlentities.php

These entity functions have no place in the logic flow prior to data base storage.  They should be used once and only once immediately before you echo the data or add the data to the browser output string.

A possible fix?
If you have entities in your data base, they probably got there because of a logic error, such as using htmlentities() on the input side of the process, rather than on the output side.  You may be able to correct the problem by reading the data base, using this:http://php.net/manual/en/function.html-entity-decode.php

I say may because once the data has been munged and stored, un-munging the data is not a certain thing.  Character encoding is one of the most complicated and nettlesome parts of information processing, so proceed with caution (and lots of data visualization) if you try to apply an automated solution to correct your data base.

best of luck, ~Ray
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now