converting from mysql to mysqli - outputting variables

Question

I have been using the mysql api in PHP, and am now converting to mysqli for the increased security. The example syntax I have seen uses printf, and I would like to know if this is necessary. At the moment I use echo, like so:

echo "<h1>".$row['ARTICLE_NAME']."</h1>
<div id='leftlayer' class='leftlayer'>
<p><strong>Username: </strong>".$row['USERNAME']."
<p><strong>Article Number: </strong>".$row['ARTICLE_NO']."
<p><strong>Subtitle: </strong>".$row['SUBTITLE']."
<p><strong>Auction Start: </strong>".$row['ACCESSSTARTS']." 
</div>";

Since with mysqli you must bind variables to the result, I have done this like so:

$getRecords->bind_result($ARTICLE_NO, $ARTICLE_NAME, $SUBTITLE$, $CURRENT_BID, $START_PRICE, $BID_COUNT, $QUANT_TOTAL, $QUANT_SOLD, $ACCESSSTARTS, $ACCESSENDS, $ACCESSORIGIN_END, $USERNAME, $BEST_BIDDER_ID, $FINISHED$, $WATCH$$, $BUYITNOW_PRICE, $PIC_URL, $PRIVATE_AUCTION, $AUCTION_TYPE, $ACCESSINSERT_DATE, $ACCESSUPDATE_DATE, $CAT_DESC$, $CAT_PATH, $ARTICLE_DESC, $COUNTRYCODE, $LOCATION$, $CONDITIONS, $REVISED$, $PAYPAL_ACCEPT, $PRE_TERMINATED, $SHIPPING_TO, $FEE_INSERTION, $FEE_FINAL$, $FEE_LISTING, $PIC_XXL$, $PIC_DIASHOW, $PIC_COUNT, $ITEM_SITE_ID);

and would like to know if I could simply replace my reference to $row with the bound variable, for example:

  <p><strong>Username: </strong>".$USERNAME."

Are there any security problems with this approach, or is it fine

Answer 1

You should escape the output when serving XML/HTML pages. For that use htmlspecialchars or htmlentities. Anyway, you should escape output regardless of the data source and the API used to fetch that data.

Answer 2

I'm not sure that binding makes your application more secure when you are getting data out of the database, however it will help when you are writing into the DB as you will have no risk of SQL injection.

Whatever approach you take to reading from the DB, you still need to escape the output using htmlspecialchars() if you are not entirely certain that the data is completely clean. The bound statement will not handle this as you suggest in your comment to Ionut - you have to escape the data in a way that is applicable to where you are outputting it. PHP/MySQL doesn't know whether you are printing into an HTML document/shell command/json/etc. There isn't an magic escape method that makes any data safe for any output medium.

Answer 3

I know that some buffer overflow vulnerabilities have been reported for certain PHP versions. I don't know which versions though. So based on this, and assuming the input isn't properly filtered, using printf may be less secure than using echo. Depends a lot on the context though.

Answer 4

I agree that mysqli is--at least in principle--better than the mysql package because you can bind parameters, which is both more secure and better for query execution. That being said, I've discovered at least two serious issues with mysqli:

• Bug 46808 is a serious problem that has been reported in different ways for at least three years and still hasn't been fixed yet. If you want to use LONGTEXT columns you may have a problem; and
• Sometimes I just get weird bugs where mysqli fails (with meaningless messages about errors in "canary form" or somesuch). It's at this point (combined with (1)) that I just had to give up on mysqli.

PDO is probably a better choice. Me? I just went back to mysql. It's hard to argue with the simplicity as long as you're careful with escaping strings and so on.