Mysqli safe insert

[Remenission]

Hi guys, I've been around here for a few years, but for some reason my other account doesn't seem to 'exist' anymore which was real annoying. I also noticed the captcha here was kind of buggy has anyone else been getting that? I'd enter it in case sensative 9-10 times before it would finally work.

 

Anyways, I've been looking through a lot of research in upgrading my server from Mysql to Mysqli funtions. What I am curious about though is other peoples opinions and thoughts on how to make user input safer. For the time being I've just been using mysql_real_escape_string and htmlspecialchars. I've done quite a bit of research on this and there really isn't much for any guides on how to keep your data clean and safe. I've seen a lot of posts that anymore these two functions are not enough to secure your data. So I'm curious what people in this community are doing (annonomysly) to keep your user input safe. I'm also looking into prepared statements as well with Mysqli. Anyways any responses are much appreciated, would love to chat with you guys about this!

 

Does anyone know if there was some deal with why I can't access my origional account? I entered in all of the only 5 email addresses I use. It said it sent an email to the one, but it never appeared in junk/inbox.

[Jacques1]

“Making the user input safe” is a rather vague requirement. Safe in what sense? The mysql_real_escape_string() function and htmlspecialchars() also have absolutely nothing to do with each other.

 

I assume you want to pass a PHP value to a MySQL query while preventing SQL injection attacks. Yes, you can use mysql_real_escape_string() for that. It works perfectly fine if you know what you're doing:

• You never forget to escape a value.
• You wrap all values in quotes.
• You do not mess with the character encoding of the database connection.

Unfortunately, this is easier said than done. A lot of PHP programmers screw up at least one of those aspects, which is why still so many application suffer from SQL injection vulnerabilities. And let's not forget that all mysql_* functions are obsolete since more than 10 years and will be removed in one of the next PHP releases.

 

Prepared statements are a much more robust approach, because they actually solve the underlying problem. SQL injections happen when the input values change the query. This is impossible with prepared statements, because they strictly separate the input from the query itself. No matter which input you pass to the statement, it's always just a harmless value.

 

So do use prepared statements. mysql_real_escape_string() was the approach of the 90s, now it's time to enter the 21st century. 

 

 

[Remenission]

How awesome! that is good to know about prepared statements. If it is impossible with them should I still use mysql_real_escape_string? Thats particularly why I'm trying to familiarize myself with mysqli, because I have heard it will be obsolete in future php releases. As far as making user input safe yeah that was my main concern.. Sql injections. I don't think it is a huge concern as I just run a couple websites for some local businesses, and most user input is stored by the employees. I'm just hoping to tighten things up with security as I move into using mysqli

[Jacques1]

If you're bound to the old MySQL extension, then you have to use mysql_real_escape_string(). There's no alternative.

 

You should go with PDO instead of MySQLi. The problem of MySQLi is that it's cumbersome as hell. And of course it's again a MySQL-specific extension which doesn't work with any other database system. PDO is a universal database interface which is compatible with all mainstream systems (PostgreSQL, MySQL, Oracle etc.) and generally provides much more comfort. There's just one pitfall: Prepared statements are disabled by default. To enable them, you have to explicitly set the PDO::ATTR_EMULATE_PREPARES option to false.