1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Converting larger number of sites from mysql to mysqli/PDO

Discussion in 'PHP' started by pmf123, Jul 2, 2015.

  1. #1
    Are there any tools out there to help me update a large number of old sites that were built with the old mysql functions?
    SEMrush
     
    pmf123, Jul 2, 2015 IP
    SEMrush
  2. malky66

    malky66 Prominent Member

    Messages:
    3,152
    Likes Received:
    1,942
    Best Answers:
    72
    Trophy Points:
    390
    #2
    malky66, Jul 2, 2015 IP
  3. pmf123

    pmf123 Well-Known Member

    Messages:
    1,322
    Likes Received:
    64
    Best Answers:
    0
    Trophy Points:
    165
    #3
    Thanks i'll take a look - have over 50 sites, was only going to do a few that make money, but i know one day i will do a PHP/Mysql update and they will all stop working
     
    pmf123, Jul 2, 2015 IP
  4. ThePHPMaster

    ThePHPMaster Well-Known Member

    Messages:
    722
    Likes Received:
    50
    Best Answers:
    31
    Trophy Points:
    150
    #4
    Another option is to use a wrapper -where once the extension is removed, you place the library and it takes care of translating the existing removed methods into PDO or MySQLI. Here is one that I wrote for PDO:

    https://github.com/AzizSaleh/mysql

    However, if it is possible to re-write the code I advise doing that.
     
    ThePHPMaster, Jul 2, 2015 IP
  5. deathshadow

    deathshadow Acclaimed Member

    Messages:
    8,386
    Likes Received:
    1,495
    Best Answers:
    220
    Trophy Points:
    515
    #5
    Just be warned most 'tools' and 'wrappers' do not ACTUALLY fix what's 'wrong' with the old mysql_ functions in the first place; that the connection is by it's nature global in scope, and that the code typically blindly pastes data values into the query string.

    Part of the conversion to either mysqli or PDO should involve switching ANY query that passes data from variables as data to prepared queries with bind or execute-passed parameters -- and I'm HIGHLY skeptical of any automated tool providing that in a meaningful fashion. The tools and wrappers just perpetuate the bad practices which resulted in those old functions being told to kiss off in the first place!

    It's like when target was deprecated so people started throwing scripttardery at recreating the functionality; COMPLETELY missing the point!

    There's a difference between making it not hemorrhage errors or warnings, and actually fixing the code. Most "tools" will only do the former.
     
    deathshadow, Jul 4, 2015 IP
    ryan_uk likes this.
  6. qwikad.com

    qwikad.com Prominent Member Affiliate Manager

    Messages:
    5,169
    Likes Received:
    960
    Best Answers:
    16
    Trophy Points:
    350
    #6
    qwikad.com, Jul 5, 2015 IP
  7. deathshadow

    deathshadow Acclaimed Member

    Messages:
    8,386
    Likes Received:
    1,495
    Best Answers:
    220
    Trophy Points:
    515
    #7
    there are a few commands that won't have 1:1 equivalents and will take some rewriting -- it should work with a bit of translation, but again it defeats the entire point of converting in the first place...

    Since everyplace you see idiotic halfwit insecure nonsense like this (even the extra variable for nothing is a waste!):

    $query="SELECT * FROM table WHERE id = '" . mysql_real_escape_string($_POST['id']) . "'";
    $result = mysql_query($query);
    Code (markup):
    What SHOULD be done is this: (assuming $mydb is an initialized mysqli object)

    $statement = $mydb->prepare('
    	SELECT *
    	FROM table
    	WHERE id = ?
    ');
    
    $statement->bind_param('i', $_POST['id']);
    $statement->execute();
    Code (markup):
    Auto-sanitizing and if the server is configured properly the query is sent separately from the data. $statement and even $mydb can also be local variables restricting the scope of database access, further letting one secure things if you know what you are doing.

    mysqli does have a procedural equivalent, but it's a wrapper for the object based version and generally speaking as a wrapper it just adds unneccessary overhead. Put on the big boy pants and do it right.

    Still, as procedural mysqli:

    $statement = mysqli_prepare($mydb, '
    	SELECT *
    	FROM table
    	WHERE id = ?
    ');
    
    mysqli_stmt_bind_param($statement, 'i', $_POST['id']);
    mysqli_stmt_execute($statement);
    Code (markup):
    ... and that doesn't even get into things like binding parameters. Really the behaviors that mysqli has that mimic the old mysql_ functions? Don't use them, it defeats the entire point of mysql_ going the way of the dodo.

    Personally, I prefer PDO.

    [code]$statement = $db->prepare('
    	SELECT *
    	FROM table
    	WHERE id = ?
    ');
    $statement->execute([$_POST['id']);
    Code (markup):
    But that's because you can pass an array of values to ->execute and it no longer ties you to JUST mysql.
     
    deathshadow, Jul 5, 2015 IP
    ryan_uk likes this.
  8. ThePHPMaster

    ThePHPMaster Well-Known Member

    Messages:
    722
    Likes Received:
    50
    Best Answers:
    31
    Trophy Points:
    150
    #8
    Would you mind elaborating on how that is insecure and how it is different when using PDO?

    From my understanding PDO has been using MySQL's native escaping functionality (same as mysql_real_escape_string) by default (unless you have
    PDO::ATTR_EMULATE_PREPARES set to false).
     
    ThePHPMaster, Jul 5, 2015 IP
  9. NetStar

    NetStar Notable Member

    Messages:
    1,898
    Likes Received:
    351
    Best Answers:
    21
    Trophy Points:
    215
    #9
    I wouldn't use a tool. Personally if you have a large number of web sites using an obsolete programming style odds are there are other dated styles on those sites.... #1 you should look at these web sites to see if they are vulnerable to SQL injection attacks... if not, don't bother to change it. If so, you are going to have to invest some time and manual labor in to updating the code if it's important to you.
     
    NetStar, Jul 5, 2015 IP
    deathshadow and ThePHPMaster like this.
  10. deathshadow

    deathshadow Acclaimed Member

    Messages:
    8,386
    Likes Received:
    1,495
    Best Answers:
    220
    Trophy Points:
    515
    #10
    Which is one of the first values I set when using PDO... If for no other reason than emulated prepares being ridiculously inefficient when doing POEM (prepare once, execute mostly) and is STILL broken on passing more than one value to LIMIT.

    Pasting values into the query string is stupid, always has been. Having the connection global in scope is stupid, always has been.

    NOT as stupid as say... putting the connection info into undeletable global scope using DEFINE like turdpress does, but it's right up there on the herpafreakingderp scale.

    After decades of working in other database scripting languages, seeing PHP dev's slap values into query strings left me wanting to pimp-slap whoever thought that was a good idea... much like seeing people wasting extra variables on NOTHING does. Hell even Clipper or dBase when you say the variable name you're saying THE VARIABLE NAME, not appending the value in the variable to the string!

    I still think that's a functionality PHP is missing, being smart enough to have a query model where if there's a $ in the query string, it treats the label that follows as a automatic bindparam to that variable WITHOUT just blindly copying the value from that var.
     
    deathshadow, Jul 5, 2015 IP