#1
  1. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,661
    Rep Power
    4123

    Pseudo Prepared Statements for mysql_


    There's been a lot of talk recently about the older mysql_* functions within PHP.

    The main gripe from those telling others not to use it is down to security.

    I've dug out an old bit of code which attempts to emulate prepared statements for the mysql_query fuction:

    php Code:
     
    <?php
     
    function mysql_prepared_query ($sql,$args=false,$con=false) {
    	if($args) {
    		if(is_array($args)) {
    			$find = $replace = array();
    			foreach($args as $key => $val) {
    				$find[] = ":".$key;
    				if($con) {
    					$replace[] = "'".mysql_real_escape_string($val,$con)."'";
    				} else {
    					$replace[] = "'".mysql_real_escape_string($val)."'";
    				}
    			}
     
    			$sql = str_replace($find,$replace,$sql);
     
    		} else {
    			throw new Exception('If $args is supplied then it must be an array, '.gettype($args).' supplied');
    		}
    	}
     
    	//for debugging, comment out in production
    	return $sql;
     
    	if($con) {
    		return mysql_query($sql,$con);
    	}
     
    	return mysql_query($sql);
    }
     
    $sql = "
    	SELECT
    		*
    	FROM
    		`table`
    	WHERE
    		`name` LIKE :name;
    		OR
    		`class` = :class
    	;
    ";
     
    $args['name'] = '%Northie%';
    $args['class'] = 'User';
     
    //debug
    echo mysql_prepared_query($sql,$args);
     
    $sql = "
    	SELECT
    		*
    	FROM
    		`table`
    	WHERE
    		`name` = :name;
    	;
    ";
    //debug
    echo mysql_prepared_query($sql,'Northie');
    I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    well, I don't really see the point of "emulating" prepared statements. I mean, if people are willing to rewrite their whole database code, they should rather choose "the real thing" and use PDO/MySQLi. Why should they take this intermediate step just to replace everything again in a few years?
  4. #3
  5. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,115
    Rep Power
    9398
    Adding a new function and trying to use it in new/touched code doesn't sound like "rewriting their whole database code" to me.
    It's not always fair to ask people to redo everything: there are a number of reasons why that might not be an option. If functions like this get them one step closer to doing it, or even if not but they realize this is more secure than what they have now, then I say it's worth it.
  6. #4
  7. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    It's a useful little function, no doubt, but if the goal is to ease the transition into PDO or MySQLi it seems like it would be better build an interface that matches the API used by PDO / MySQLi - ie with prepare and execute. That way if they ever do actually decide to fully convert to the superior interfaces they won't have to rewrite as much code using your compatibility layer.

    Also it's worth noting that the behavior of this function is not fully compatible with real prepared statements. It serves the same purpose as far as security goes, but this function allows things that normal prepared statement would not.

    I think a bigger problem with the MySQL extension is that it's deprecated and slow. It's not actually that difficult to write secure code using them, as you've demonstrated.

    Comments on this post

    • ManiacDan agrees : "I think a bigger problem with the MySQL extension is that it's deprecated and slow" <-- that
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around

IMN logo majestic logo threadwatch logo seochat tools logo