When to use mysqli_real_escape_string

PHP
1

If I have a situation where the user will input a name such as a city and then I store that in a session variable and use it multiple times to create sql queries during the session, can I use mysqli_real_escape_string at the time I capture the city name the first time and put it in the session variable rather than using it every time I perform a query?

Also, if I were to store that city name in a database for future use, if I use mysqli_real_escape_string before I store it in the database, can I then retrieve that city name in the future without using mysqli_real_escape_string again the next time I use it in a query?

2

If you use prepare statements then you will NEVER need to use mysqli_real_escape_string as prepare statements will ensure that the query and the data are completely separate and the data can’t be used to inject code into the query instead of having to escape the data in order for SQL to be able to tell the difference.

3

Thanks for the input. I am off studying the PDO now and plan to head that way so you are right, once I do that this point is moot. But definitely the way to go. Thanks again.

4

Got it. So there is no real advantage to using PDO in this case? I have no need for transactions and all the database stuff is pretty straight forward, selects, inserts, updates, etc.

One more question, how do I prepare a select statement using an in statement when I build that in statement dynamically? Select * from tbl1 where fld1 in (x, x, x, x, x, x, x…) and I am not sure until runtime how many x’s there will be. Thanks for the input.

5

You don’t need PDO if you are using mysqli because mysqli unlike mysql supports prepare statements directly.

I recommend NOT creating a persistent connection to the database - it can cause lots of problems and provides almost no benefits.

6

Although one question regarding that whole subject. :slight_smile:

If I create a persistent connection to the database and prepare a query once, run and leave that php script, if I come back in, do I need to prepare it again or can I just use it since the connection is persistent?

And is there a downside to a persistent connection?

Thanks