Should I use prepared statements in while loops?

[eldan88]

I am revisiting my code to refactor it and debating if whether or not I should use mysql prepared statements. The only goal I am trying to accomplish, is to simply display menu items for a restaurant food ordering website. That's all. It will select menu items from a database and display them using a while loop.

 

Upon reading the documentation in php's website. They said that prepared statements are more efficient when statements are repeated.

But all I am doing is querying the table once and preforming a while loop when a user visits my page.

So is it really necessary to use prepared statements other than the fact that is prevents SQL injection?

 

[fastsol]

Prepared statements are used for things that are user defined variables.  If you are defining the query to build the menu then you would just do a normal SELECT query.

[requinix]

Prepared statements are good for two situations:

1. Repeating the same basic query many times but with different data each time. Inserting data from a CSV file is a good example: the structure of the query is the same for every row, but the data varies.

2. When you don't want to deal with escaping values. It trades slightly less performance for a more-or-less guarantee that you won't be susceptible to SQL injection.

 

You say there's one query? #1 won't help much but #2 may.

[eldan88]

oh okay. I understand. Thanks guys!

[eldan88]

Requinix. I have a quick question. How is select statement prone to SQl injection when it doesn't preform any kind of inserting into the DB?

[Psycho]

Requinix. I have a quick question. How is select statement prone to SQl injection when it doesn't preform any kind of inserting into the DB?

 

You obviously don't know what SQL Injection is. You should really go read an article or two to understand it. It has nothing to do with INSERTing records in the database. It has to do with injecting unintended SQL code into the query statement (SELECT, INSERT, UPDATE, DELETE, etc.)

[gizmola]

SQL injections are basically a situation where the user is able to "inject" a modification to the SQL statement via input. Here's a simple example:

 

$sql = "SELECT * from MEMBER WHERE account = '$account' AND password = '$password'";

This is a somewhat typical login statement, where someone will provide their account name and password to a form, and you accept input and build your sql statement. Then based on the return of a row, people will often accept the login as valid and set the user's session variables with the account information.

 

BUT!!!! What if the user sends this as input ---

 

' OR '1' = '1

Now when the password variable gets interpolated the SQL string becomes something totally different than what you originally intended.

 

For this reason, you typically have to be extremely careful about escaping input variables so that these types of injections can not get into the code.

 

It's just much simpler and safer not to have to deal with escaping at all, and instead, to use prepared statements with bound variables anyplace where you're going to take user input and use that as the basis of a query, whether that be a select or an insert or update.