How does sanitization for php/mysql work?

Question

So, I've finished a great deal of the site I am working on, but I want to sanitize the data from the user now.

The website is an auction style website. A form exists which asks the user for data about an item they wish to auction off. When the form POSTs, the data is entered into variables in php, then submitted to a database, as such:

if(isset($_POST['title'])) {

            $allowedExts = array("gif", "jpeg", "jpg", "png");//array of allowed extensions
            $temp = explode(".", $_FILES["file"]["name"]);
            $extension = end($temp);

            /*
            * Set variables for the _POST data (except the file).
            */
            $title = $_POST['title'];
            $description = $_POST['description'];
            $type = $_POST['description'];
            $startPrice = $_POST['startPrice'];
            $reservePrice = $_POST['reservePrice'];
            $buyPrice = $_POST['buyPrice'];

It then verifies that the file uploaded with the form is an image of a valid format and size, copies it to its permanent location, and sets the URL variable for the image.

Then it enters the database like this:

if (!$this->db->query("INSERT INTO tbl_auction_listing VALUES(NULL, '$userid', '$title', '$description', '$img_url', '16', NOW(), NOW() + INTERVAL 5 DAY, '$startPrice', '$reservePrice', '$buyPrice', '0', '1')")) {
                            echo '<h2> Item did not enter successfully, please try again </h2>';
                            //redirect(site_url() . 'user/fail/'); exit();
                        }

Its quite important that a user not be able to use any custom HTML, or escape the current command in php so that they can execute shell commands.

Anyway, once the item data is retrieved later from the db, it is formatted similar to this (some lines omitted, just wanting you to see how it's coded for example)

$query = $this->db->query("SELECT * FROM item{$item} WHERE winning = '1';");
        foreach ($query->result() as $row)
        {
            $currentHighBid = $row->bid;
            // if the row is null, it means there's no bids- initialize variables and set the qualifying bid to 100 satoshi.
            if ($currentHighBid == NULl) {
                $currentHighBid = NULL;
                $currentWinner = NULL;
                $winnerMaxBid = NULL;
                $ip = NULL;
                $winning = NULL;
                $qualifyingbid = $startprice;
                $displayQualifyingBid = number_format($qualifyingbid, 8, '.', ''); // format to decimal places rather than scientific notation
            }
            else {
                $currentHighBid = $row->bid;
                $currentWinner = $row->user_pk;
                $winnerMaxBid = $row->maxbid;
                $ip = $row->ip;
                $winning = $row->winning;
                $qualifyingbid = $currentHighBid * 1.01; // the next allowed bid should be 1% higher than the current bid
                $displayQualifyingBid = number_format($qualifyingbid, 8, '.', ''); // format to decimal places rather than scientific notation
            }
        if ($currentWinner == $userid && $open == 0) //fix for undefined
            echo "  <h2 class='notice'> You won! Congratulations!</h2>";
            //enter the pay form here
        if ($currentWinner == $userid && $open == 1) //fix for undefined
            echo "  <h2 class='notice'> You are currently the high bidder.</h2>";
            //enter the pay form here
        if ($currentHighBid == NULL) //fix for undefined
            echo "  <h2 class='notice'> The item currently has no bids</h2>";
        else 
            echo "  <h2 class='price'> The current bid is $currentHighBid BTC <a href='/listings/bids/$item'>(Click for Bid History)</a></h2>";
        if ($open == 1)
            echo "  <h2 class='price'> The next acceptable bid is $displayQualifyingBid BTC</h2>";
        if ($remaining > 0) 
            echo "<h2 class='notice'> This item has $remaining remaining.</h2>";
        }

How do I make sure these are escaped\sanitized properly?

To prevent malicious code injection, look into [prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). To prevent HTML injection, use [htmlspecialchars](http://php.net/manual/en/function.htmlspecialchars.php). — wavemode, Sep 27 '14 at 03:34
Don't. You can theoretically use [mysql_real_escape_string](http://php.net/manual/en/function.mysql-real-escape-string.php), but it's so easy to forget to do, and if you forget just once then your site is insecure. Use [prepared statements](http://php.net/manual/en/pdo.prepared-statements.php) — Jason Baker, Sep 27 '14 at 03:35
a side note, the framework is codeigniter, does that make a difference in implimentation? — user3175451, Sep 27 '14 at 03:38

score 0 · Answer 1 · answered Sep 27 '14 at 03:36

How about a function that helps

function cleanInput($input) {
    $search = array(
        '@<script[^>]*?>.*?</script>@si',   // Strip out javascript
        '@<[\/\!]*?[^<>]*?>@si',            // Strip out HTML tags
        '@<style[^>]*?>.*?</style>@siU',    // Strip style tags properly
        '@<![\s\S]*?--[ \t\n\r]*>@'         // Strip multi-line comments
    );
    $output = preg_replace($search, '', $input);
    return $output;
}

How does sanitization for php/mysql work?

1 Answers1