[RESOLVED] overly paranoid to avoid mysqli_real_escape_string in favor of prepared statements?

Bjom

I want to store the results of a questionnaire to a DB. Along with the data for each question I need to store info about who was being assessed and who assessed the person. That would be personIDs.

I'll have about 30 checkboxes per page of which 0 to 20 can be checked. I plan to store the info in a table like this:

AssessorID | CandidateID | AnswerID | Value

I plan to loop through the POST array and generate 0 to 20 records in that table.

The info for AssessorID and CandidateID will be the same per page that means the same for all the 0 to 20 entries I generate.

I have thought of the following two approaches:

a) make a prepared stmt with 4 parameters thus creating redundant data transfer for the two IDs, because they get sent along each pass

$sqla = sprintf('INSERT INTO tbl (AssessorID, CandidateID, AnswerID, Value) VALUES (?,?,?,?)');

b) make a prepared statement that includes the IDs already, making it necessary to escape those before integrating them into the statement like so:

$sqlb = sprintf('INSERT INTO tbl (AssessorID, CandidateID, AnswerID, Value) VALUES (%s,%s,?,?)', $dbConn->real_escape_string($aID), $dbConn->real_escape_string($cID));

b) seems to be more efficient to me. However I have so far avoided the use of mysqli_real_escape_string since the prepared statements are supposed to be the most secure way and now I'm not sure if there is something more to do or to think of. Is that being overly paranoid? And another question: any other flaws in my approach(es)?

any suggestions welcome

Bjom

laserlight

Actually, if you are thinking of executing the same SQL statement up to 20 times but with different values, then a prepared statement fits the bill: you prepare the statement once, then bind to parameters and execute as many times as you need. Unless the database engine caches the statement (which admittedly is likely for MySQL), this is more efficient than executing so many times from scratch. You should only need to rebind those parameters which change (but I could be mistaken).

Weedpacket

I'd say what laserlight said, but laserlight has already said it. However, if these IDs are meant to be integers then casting them to int, or using %d instead of %s would ensure that nothing really bad got in. Admittedly, there is a risk that something bad would be converted by either mechanism into something that's legal (i.e., won't break the query) but an invalid/illicit value.

Thing is, this is just an insert, right? Unless this table is actually a view and/or there are a bunch of triggers hiding behind the statement, there's not a lot of query execution planning going on here. Data arrives, it gets stuck on the end of the table. Nor would index updating be any different using either method.

Bjom

Thanks guys!

The IDs might be integers or char(32)...depending on the project.

Admittedly, there is a risk that something bad would be converted by either mechanism into something that's legal (i.e., won't break the query) but an invalid/illicit value.

Thanks for confirming this.

So it boils down to either having a (presumably low) risk for something bad slipping through the conversions or creating (presumably little) extra data traffic.

Now that I look at it again neither seems too bad. I think I'll go with whatever produces easier to maintain code.

You should only need to rebind those parameters which change (but I could be mistaken).

With mysqli I can't see how, actually, since the parameters are not named, which in turn means you need to pass all of them and in the right order when calling the bind_param function. Wish they would improve on that with named params.

Cheers

Bjom