how to correctly use mysqli_real_escape_string() Function in a query

Question

I am trying to use the mysqli_real_escape_string() Function in a query.

This is my current code:

 $Product_Id = substr($prod_name, 14, (strlen($prod_name)-14));
 $get_query = "SELECT P FROM Product WHERE Product_Id =' .mysql_real_escape_string((int)$Product_Id))";

This does not work, it creates the following error:

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in

But if i remove the mysqli_real_escape_string function my code works.

So what is the best way to do this, as i am trying to stop sql injection.

Little Bobby says your script is at risk for SQL Injection Attacks.. Even escaping the string is not safe! Learn about prepared statements for MySQLi. — Jay Blanchard
– Jay Blanchard, Commented Apr 21, 2016 at 15:21
You have to learn the difference between SQL and PHP since they are different languages. mysql_real_escape_string() is a PHP function, not a SQL function. — Álvaro González
– Álvaro González, Commented Apr 21, 2016 at 15:21
imo, please don't use any mysql*_ functions at all. Also, never use mysqli_real_escape_string or PDO Quote functions. imo, always use prepared queries with placeholders whenever you are using variables, supplied from a user, in an SQL statement. — Ryan Vincent
– Ryan Vincent, Commented Apr 21, 2016 at 15:33
Maybe you should look at PDO... With predefined queries you shouldn't care about SQL injections. — LibertyPaul
– LibertyPaul, Commented Apr 21, 2016 at 15:36

mferly · Accepted Answer · 2016-04-21 15:35:45Z

1

I am trying to use the mysqli_real_escape_string() Function in a query.

But you're actually not. In your query you're using mysql_real_escape_string().

Plus that query is malformed, so it wouldn't work anyway. Your quotes are in the wrong places. Try the following:

$get_query = "SELECT P FROM Product WHERE Product_Id = " . (int) mysqli_real_escape_string($Product_Id);

Since $Product_Id is being cast to an integer, you won't need to wrap it in quotes within the query (assuming Product_Id column is integer-based; since you're casting it to an integer, I'm assuming it is).

And moving the type cast (int) from the argument within mysqli_real_escape_string() to actually preceding the function is what you're looking for. Although it's not necessary to cast $Product_Id at this time as it is redundant and could actually pose more problems than it'd solve in some circumstances (Ie. assume $Product_Id was somehow set to a string [$Product_Id = 'Marcus'], and you then cast it to an integer: (int) $Product_Id it'd return 0, but no error). A negative integer would also slip through which I'm assuming you don't have negative $Product_Id's, right? There are much better ways to detect, and handle, variable types prior to sending them to a query. But we can get into that another time.

In your query you had an erroneous single-quote (WHERE Product_Id =') which was causing a parsing error.

edited Apr 21, 2016 at 15:35

answered Apr 21, 2016 at 15:28

mferly

1,6561 gold badge13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Monroe Over a year ago

Thank you, for that. I am a newbie following a tutorial

Collectives™ on Stack Overflow

how to correctly use mysqli_real_escape_string() Function in a query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related