Hm

[waynew]

When should I use prepared statements? I mean, I know the advantages they give in regards to security and multiple queries, but should I really go using them on every query that involves external data, and thus, end up with slower Req/Sec?

 

Forgive me if I'm wrong (and certainly correct me if I'm wrong), but I reckon that the below piece of code is secure enough on it's own?

$username = $mysqli_real_escape_string($mysqli,$username);
$check_username_query = "SELECT COUNT(*) FROM user WHERE username = '$username'";

[jackpf]

I personally escape everything and stick the query into one line...eg,

mysql_query("SELECT * FROM....

 

Is there really much of a security issue with this..?

[Daniel0]

When should I use prepared statements?

 

One of the advantages it has if you are going to run the query repeatedly then you'll see a performance boost because it's already prepared. So it's excellent for things like batch processing. The parametrized queries do not have any advantage over escaping values and interpolating them into the query string in terms of security. Of course if you make a habit of using parametrized queries then you will not accidentally forget escaping tainted data, so I suppose that it in that sense is more secure.

 

Is there really much of a security issue with this..?

 

No.

[waynew]

That's what I was thinking in regards to security. I mean, I'm pretty OCD about incoming data and I'm proud to say that I've never discovered that I've forgotten to clean something etc. I white list a lot of the time, so I maybe it's a good thing that I don't use them, unless of course, I'm doing multiple updates on the same table etc.

 

Thanks.

[PFMaBiSmAd]

On the subject of "stick the query into one line..."

 

In a real application, when a query fails and even for some successful queries (so you can see what your visitors are searching for in your database), you would log all the information available about the query, including the actual query string. Forming the query in a separate variable makes it directly possible to log the actual query string without repeating it in the logging code.

[Daniel0]

That's what abstraction is for.

 

PDO will throw an exception that you can catch and pass to another place that handles errors in different ways depending on whether you're in a production or development environment.

[waynew]

I'd really only use the PDO if I was building a site that wasn't exclusively meant to be used with MySQL. Although that may seem a little short-sighted on my part, the drop in performance is just too much of a deterrent for me.

[Daniel0]

Well, you can catch regular errors using set_error_handler as well.