The topic of the difference between addslashes() and mysql_escape_string() has come up a few times as of late, and I found myself actually having to look up the answer. I knew (as I hope you do) that the mysql_ version was better (mysql_real_escape_string() being better still), but I couldn't remember exactly why. In my research I found a bunch of miscellaneous blog posts that simply agreed with my understanding that mysql_escape_string() > addslashes(). Finally I found a post at NyPHP (of which I am a member) which described it in a bit more detail, listing some of the meta-characters that MySQL understands, but it didn't go into detail on exactly which characters were escaped. A chart, indicating my findings comparing addslashes to mysql_escape_string (all three sources of information are shown). I will consider the source and my test definitive (results after the jump).

		addslashes()	mysql_escape_string()
Ascii	Name	testing	testing	source
0	Null
8	Backspace
9	Tab
10	\n
13	\r
26	Substitute
34	"
39	'
92	\

So in conclusion: Use The Source! and you should use mysql_escape_string() over addslashes() to escape three additional characters, newline, carriage return, and substitute character.
Notes:
My test consisted of iterating through the ascii character chart, and comparing chr($i) to mysql_escape_string(chr($i)) then reporting if they didn't match (as would happen when something is escaped. Reading the source was looking at escape_string_for_mysql() in charset.c (part of mysql 5.0.45), and mysql_sub_escape_string() in libmysql.c shipped with the PHP 4 branch. The NyPHP document indicates that mysql_escape_string() and mysql_real_escape_string() all escape those same characters, it may be true that the real version escapes more, I intend to test more in the future.

Note^2
You shouldn't actually be using mysql_escape_string() you should be using mysql_real_escape_string() which takes the character set into account.

Currently, most database driven apps install using something like this:

$username = $_POST['username'];
$password = $_POST['password'];
mysql_connect('localhost', $username, $password);
mysql_query('CREATE TABLE a ...');
mysql_query('CREATE TABLE b ...');
mysql_query('CREATE TABLE c ...');
file_put_contents('./includes/local.ini.php', $username . ',' . $password);

I however wish the instalation worked more like this:

$username = $_POST['username'];
$password = $_POST['password'];
$roPassword = uniqid('', true);
$rwPassword = uniqid('', true);
mysql_connect('localhost', $username, $password);
mysql_query('CREATE TABLE a ...');
mysql_query('CREATE TABLE b ...');
mysql_query('CREATE TABLE c ...');
mysql_query("GRANT select ON db.a TO 'ro'@'localhost' identified by '$roPassword'");
mysql_query("GRANT select, update, delete,insert ON db.* to 'rw'@'localhost' identified by '$rwPassword'");
file_put_contents('../appNameIncludes/roAccount.txt', 'ro' . ',' . $roPassword);
file_put_contents('../appNameIncludes/rwAccount.txt', 'rw' . ',' . $rwPassword);

The critical differences between the two are:

• Not storing the username and password the user provided for long term use.



Most users are lazy, and pretty generally uninformed when it comes to security. As such they use either their root account, or whatever account their ISP provided to them for all their applications, this means that should a vulnerability be discovered the entire database is vulnerable. Under this set up the credentials the user provided are used only for the initial set up, then discarded when the script terminates.



• Using separate accounts for read only and write access



This has two advantages, first of all it does some work to limit the damage caused by possible SQL injection attacks in the vast majority of your code (most web apps are SELECT rather than INSERT or UPDATE dominant). Second, it works well if your application needs to scale to where writes go to one server and reads another.



• Storing the include files outside the doc root (hopefully)



The only reason to store documents within the web root is because end users need to load them directly, if they're simply include files store them elsewhere.

Now you may be quick to point out that the approach requires the database user to have Grant privileges, which aren't given to users created simply with GRANT ALL, that's true. However: I believe that many users are running with privileged accounts anyways, and failing over with an error message or request to the user would be trivial.

Limitations

This approach isn't perfect, if I could rely on the user to be able to make more complicated decisions, or affect changes on their server I would also:

• Recommend modifying the credential files



Change the credential files to a format accessible by the webserver, change them to read only, and only accessible by root, then have the webserver (apache) load up the files during start up. The credentials would then be available in the $_SERVER super global. This method is described here in the PHP Security Guide



• Suggest query limits



MySQL and most other database systems are capable of limiting user accounts over a series of different criteria (Max queries per hour, updates per hour, connections per hour, user connections). Using realistic limits here can help ensure that attacks don't consume all of the systems resources.

I've glossed over some basic stuff there, I didn't bother listing the db selection, or running the data through an escaping function. Instead I chose to concentrate on the areas in which I think we can make some improvement

I've mentioned in the Pro::PHP Podcast a few times how I feel that Facebook should have done a bit more to either re-evaluate their security model when they opened up registrations, or at least educate their user-base that anything you post is (under default settings) completely open if you're a member of an open network (such as your city).

They need to do it again.

Facebook offers the ability to add someone as a friend, but only allow them to view your "Limited Profile", so rather than rejecting someone as a friend outright, you can limit what they have access to. For instance, if some random person from college sent you a friend request that you didn't really want to completely open your life to, you could add them as a friend but not share your friends list, wall, or whatever with them.

The Re-evaluation needs to happen now that they've opened up their API and are encouraging people to develop Facebook applications. These applications allow you to modify your profile page, add little widgets to display pretty pictures, let people draw on your wall, throw snowballs, murder kittens, all sorts of stuff. Now these applications appear to be generally visible, even to people who you've restricted via the limited profile option. So, if for example you had restricted someone to not seeing your wall via the limited profile setting, then installed the "Advanced Wall" application and encouraged your friends to use it, the limited profile step was pointless since the person can see your advanced wall (and have a guess at many of your friends).

Social networking sites are forced to constantly balance the privacy of their members with the goal to be open so their members can interact with each other. All of that while working with a user-base that isn't (and doesn't want to be) educated with respect to privacy. As such, I can accept that by default custom applications are visible, however if someone has already made the conscious choice to limit the information a given user should see, the model should flip, that amount of information available to that user should only ever decrease unless the account owner gets involved to specifically grant access to a given element.

Edit:
Some people have pointed out that this is the privacy model, not the security model, a statement I disagree with (while I can accept the distinction, security is a superset including privacy and several other elements). I would also state that users who feel you aren't respecting their privacy are far more likely to quit than to try and understand your security model.

Google's goal is to not be evil, however, that's exactly what they're being right now, and likely not for the reasons you're thinking of. If you head over to Blogger and attempt to comment on someone's blog, you may be prompted with a message like the one below:

In order to authenticate to blogger you are required to submit a username and a password, that's... okay. However the evil thing here is that blogger is asking you to provide access credentials from Google. Now you and I may be aware that Blogger is a Google property, it's got some great company (Google Spreadsheets, Picassa Web Albums, Google Groups, etc.), but chances are my mom isn't, neither does yours, and really a big chunk of the web surfing public doesn't either. The difference between Google Spreadsheets, Picassa web albums, etc and Blogger is the url. If you click on any one of those links you're going to end up at something.google.com. Blogger is on a completely different domain.

By instructing people to enter access credentials on one site, generally associated with another Google is training people to get phished.

I sympathize with Google, I really do. We all have far too many sets of usernames and passwords in our heads already, and some sort of Single Sign On (SSO) solution would be great. The problem is that we can't merely ask users to authenticate with some apparently unrelated set of credentials, that simply trains the user to enter whatever access credentials are requested (I could quite easily make my blog authenticate off eBay, want to buy a broken watch for $500? If you submit a comment you just might). There needs to be a clear domain based relationship between what I see in my address bar, and the site I am at (a little logo isn't going to cut it). I think the only real solution without merging properties (and they clearly want to allow Blogger to maintain it's own brand image) is to allow people to authenticate within a pop-up window loaded off google.com, over a secure connection.