Transactions using mysql_real_escape_string

Question

Working with MySQL lately, from PHP, I am wondering about this:

• What is the performance impact by using mysql_real_escape_string() multiple times at a script?
• Is it worth to try to reduce the number of calls to this function for a given script?
• Does it determines the character set of the connection each time is called, or this value is cached?

If a scenario is needed, I'm thinking about PHP, and distinction between text and numbers, where numbers (using intval(), floatval() or direct casts) can be included without a call.

Answer 1

If you need to escape user input prior to database entry then you will have to use mysql_real_escape_string() ... don't worry too much about premature optimization.

Alternatively, you can look into prepared statements which will save you having to call this function multiple times - and it is more secure as it separates SQL logic from user input altogether.

Answer 2

Don't be penny-wise and pound-foolish.

Your questions are in the realm of micro-optimizations. Creating a needed index or caching some query result will have an order of magnitude more benefit than worrying about the performance impact of a few calls to mysql_real_escape_string().

By the way, typecasting with (int) $variable is slightly faster than calling intval($variable). But this too would be a micro-optimization.