Mysqli conversion advice

Question

I'm having to update my PHP for the new format but I'm confused with the codes that are given online. At the moment I am using plain mysql but I would like to update it to mysqli. Here is an example of one peice of code that I have:

$checked= mysql_real_escape_string($_REQUEST["checked"]);

I thought that it may just be a case of changing it the mysqli_real_escape_string but after looking at a few example codes I found that this is not the only solution - mysqli::real_eacape and mysqli->real_escape are also listed. Which one is correct?

Thank You

Removing it completely and using prepared statements is the correct solution. — jeroen, Mar 21 '13 at 18:08

TonyArra · Answer 1 · 2013-03-21T19:28:54.533

2

Have a look at this http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

Prepared statements are the proper way to sanitize database queries, and it's the method preferred in mysqli.

To answer your comments to my post:

Notice that there are two ways of calling it: Object Oriented and Procedural.

OOP usage means first creating a mysqli object like this:

$mysqli = new mysqli("localhost", "my_user", "my_password", "db");

and then calling the escape function like this:

$mysqli->real_escape_string($unchecked_string);

The Procedural way is done by first connecting to the database like this:

$link = mysqli_connect("localhost", "my_user", "my_password", "db");

And then calling the escape function like this:

mysqli_real_escape_string($link, $unchecked_string);

Using OOP and prepared statements is the preferred way of working with mysqli.

edited Mar 21 '13 at 19:28

answered Mar 21 '13 at 18:11

TonyArra

10,607
1
30
46

Thanks, but if I dind't want to use prepared statements is mysqli_real_escape_string acceptable? – user2184018 Mar 21 '13 at 18:15
Not exactly. Mysqli_real_escape_string doesn't actually perfectly sanitize your queries, and it still leaves you open to SQL injection attacks. Found a good explanation of it here: http://stackoverflow.com/questions/5414731/are-mysql-real-escape-string-and-mysql-escape-string-sufficient-for-app-secu Hence, you should be using prepared statements in either mysqli or PDO, as escaping the query is fairly pointless on its own. – TonyArra Mar 21 '13 at 18:35
Well what I'm trying to do is to escape a request which was working before but now I'm getting a warning "Warning: mysqli_escape_string() expects exactly 2 parameters, 1 given in C:" for $username= mysqli_real_escape_string($_REQUEST["username"]); – user2184018 Mar 21 '13 at 18:46
Notice that there are two ways of calling it: Object Oriented and Procedural. Using it that way is procedural, and is of this format: mysqli_real_escape_string ( mysqli $link , string $escapestr ) The $link parameter is the return value you get from mysqli_connect – TonyArra Mar 21 '13 at 19:16
Thank you, yes I've noticed that you have to use the connect statement quite a lot in mysqli - Out of curiosity, is it ok to mix it up slightly and use both types of code or is it if you commit to one type it has to be that one through your entire coding of your site? – user2184018 Mar 21 '13 at 20:55
Well once you connect (either using the procedural mysqli_connect or creating a mysqli object), you need to stick to using that particular style. The OOP way by using a mysqli object is just much cleaner. Less code for you. – TonyArra Mar 21 '13 at 23:29
Thanks @Ttony21, I'll continue to do this project using this method and then look int OOP for future projects – user2184018 Mar 22 '13 at 09:20

score 0 · Answer 2 · answered Mar 21 '13 at 18:12

I prefer PDO to mysqli and its prepared statements escape strings automatically: Just fill in DB_NAME, DB_USER_NAME, and DB_USER_PASS with your connection credentials:

$con = new PDO( 'mysql:host=localhost;dbname=DB_NAME;charset=UTF-8', 'DB_USER_NAME', 'DB_USER_PASS' );

$query = $con->prepare( "SELECT `field` FROM `tbl_name` WHERE `field` = ?" );
$query->bindValue( 1, $field_value );
$query->execute();

if( $query->rowCount() > 0 ) { # If rows are found for query
     echo "Field value found!";
}
else {
     echo "Field value not found!";
}

The prepare and bindValue methods automatically handle escaping data.

score -1 · Accepted Answer · answered Mar 21 '13 at 18:19

-1

The correct is for procedural style

mysqli_real_escape_string

and for OOP style

 $mysqli->real_escape_string()

where $mysqli is just a random variable name

answered Mar 21 '13 at 18:19

Nikitas

1,013
13
27

Thank you - After only just really learning PHP it feels like I'm almost having to start over again – user2184018 Mar 21 '13 at 18:22
1

The best is to use PDO rather than mysqli as BIT CHEETAH post says. PDO is the cutting age. – Nikitas Mar 21 '13 at 18:25

Mysqli conversion advice

3 Answers3