Page 1 of 1

SQL sanitation

Posted: Mon Jun 10, 2013 7:41 am
by FrederickGeek8
Helx's topic brought to my attention that mysql_real_escape_string has been deprecated. Right now I'm working on a project that I want a good amount of security for so if I need to, I want to replace all the instances of mysql_real_escape_string. What functions should be used instead of mysql_real_escape_string. On PHP.net they suggest mysqli_real_escape_string but I have no idea what MySQLi is. I've been using phpmyadmin and MySQL for a long time now and I just have no idea what I'm doing.

If I could have some help that would be great.

Re: SQL sanitation

Posted: Mon Jun 10, 2013 11:18 am
by Temor
As far as I know, mysql_real_escape_string is still fine to use since it does the same exact thing as mysqli_real_escape_string or the pdo version of it.

Mysqli stands for Mysql Improved. It's a class with everything you need to handle Mysql.
It's not too difficult to start using. Take an hour or two and play around with the different functions and you'll notice that the syntax remains pretty much unchanged.

Instead of [syntax=php]mysql_real_escape_string($string);[/syntax]
you'd do [syntax=php]$mysql->real_escape_string($string);[/syntax]
or $mysqli if you prefer.






I still use the regular old boring mysql_ functions in every case except where I HAVE to use OOP.

Re: SQL sanitation

Posted: Mon Jun 10, 2013 2:23 pm
by FrederickGeek8
Seeing as MySQL functions are going to be removed in the future I want to switch over. The less maintenance, the better.

Also, using the normal file-structure in Jacek's videos, how would I sanitize and execute SQL in function stored in different files?

Re: SQL sanitation

Posted: Mon Jun 10, 2013 4:26 pm
by Temor
FrederickGeek8 wrote:Seeing as MySQL functions are going to be removed in the future I want to switch over. The less maintenance, the better.

They are?! That's more than I knew.

FrederickGeek8 wrote:Also, using the normal file-structure in Jacek's videos, how would I sanitize and execute SQL in function stored in different files?

You only need to sanitize the input, and you do that in the same way you do now.
[syntax=php]$string = $mysql->real_escape_string($string);[/syntax]

Re: SQL sanitation

Posted: Mon Jun 10, 2013 11:17 pm
by FrederickGeek8
So would this work?
[syntax=php]<?php
$mysql = new mysqli('127.0.0.1', 'user', 'pass', 'blogg');
include('otherfile.inc.php');
add_post('Title', 'Body');
?>[/syntax]

otherfile.inc.php
[syntax=php]<?php
function add_post($title, $body){
$title = $mysql->realescapestring(htmlentities($title));
$body = $mysql->realescapestring(htmlentities($body));
$mysql->query("INSERT INTO `posts` (`title`, `body`) VALUES ('{$title}', '{$body}')");
}
?>[/syntax]


Also, is there anything special I really need to install. Do I need to transfer databases or tables or anything?

Re: SQL sanitation

Posted: Tue Jun 11, 2013 1:53 am
by Temor
Try it for your self? :)
Everything comes pre-installed, so all you need to do is write the code and run it.

Re: SQL sanitation

Posted: Tue Jun 11, 2013 6:21 am
by FrederickGeek8
It doesn't transmit $mysql. As a result I get these errors
[syntax=text]
Notice: Undefined variable: mysql in /var/www/test/functions.inc.php on line 3

Fatal error: Call to a member function realescapestring() on a non-object in /var/www/test/functions.inc.php on line 3[/syntax]

Re: SQL sanitation

Posted: Tue Jun 11, 2013 6:28 am
by Helx

Re: SQL sanitation

Posted: Tue Jun 11, 2013 6:31 am
by FrederickGeek8

Forgot the underscores but thats not the big problem here... The problem is, is that I cannot use the existing connection to the database in my functions on another file.

So now the error is just
[syntax=text]
Notice: Undefined variable: mysql in /var/www/test/functions.inc.php on line 3

Fatal error: Call to a member function real_escape_string() on a non-object in /var/www/test/functions.inc.php on line 3[/syntax]

Re: SQL sanitation

Posted: Fri Jun 14, 2013 9:23 pm
by FrederickGeek8
I got it to work by doing this:
[syntax=php]function add_post($title, $body){
/*This is what I added*/global $mysql;
$title = $mysql->real_escape_string(htmlentities($title));
$body = $mysql->real_escape_string(htmlentities($body));
$mysql->query("INSERT INTO `posts` (`post_title`, `post_body`) VALUEs ('{$title}', '{$body}')");[/syntax]