dcsimg
www.webdeveloper.com
Results 1 to 10 of 10

Thread: Change mysql_query to PDO

  1. #1
    Join Date
    Aug 2017
    Posts
    13

    Change mysql_query to PDO

    I like to change all mysql_query to PDO and now I have this,


    PHP Code:
    <?php
     
    try {
    $db = new PDO("mysql:host=localhost;dbname=dbname;charset=utf8;""user""pass");
    } catch (
    PDOException $e) {
    die(
    "Could not connect to database");
    }
     
    ?>


    My question is how do I convert this to work with PDO, I can't seem to find the right code.



    PHP Code:
    $vtest mysql_query("SELECT d1, d2, d3 FROM items WHERE item='Afdeling'");
        
        


    while (
    $row mysql_fetch_array($vtest)){
        
    $doen1 $row['d1']; 
        
    $doen2 $row['d2'];
        
    $doen3 $row['d3'];
        

    }
        

    if (
    $doen1 !=NULL){   
    $xtest mysql_query("SELECT * FROM $doen1");
        
        


    while (
    $row mysql_fetch_object($xtest)){
      echo 
    '<p><a href=""><div class="namen">'.$row->naam.'</div></a></p>'
        
       

    }


    Any help would be appreciated.

  2. #2
    Join Date
    Apr 2017
    Posts
    67
    Study this PDO Tutorial https://phpdelusions.net/pdo

  3. #3
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    22,236
    A straight-forward, simplistic translation would be something like this, though it's not how I'd ultimately do it -- including worrying about the danger of using a DB value as a table name in a query, which besides of security concerns suggests a less than optimal DB design.

    PHP Code:
    <?php
     
    try {
        
    $db = new PDO("mysql:host=localhost;dbname=dbname;charset=utf8;""user""pass");
    } catch (
    PDOException $e) {
        die(
    "Could not connect to database");


    $vtest $db->query("SELECT d1, d2, d3 FROM items WHERE item='Afdeling'");

    while (
    $row $vtest->fetch(PDO::FETCH_ASSOC)) {
        
    $doen1 $row['d1']; 
        
    $doen2 $row['d2'];
        
    $doen3 $row['d3'];
    }
        
    if (
    $doen1 !=NULL){   
        
    $xtest $db->query("SELECT * FROM $doen1");
        while (
    $row $xtest->fetch(PDO::FETCH_ASSOC)) {
            echo 
    '<p><a href=""><div class="namen">'.$row['naam'].'</div></a></p>'
        }
    }
    UNTESTED!
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

  4. #4
    Join Date
    Aug 2017
    Posts
    13
    @NogDog Thank you for your reply it works, but now it seem I have a security problem as you said!

    What would you suggest I do to make it more secure?

  5. #5
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    22,236
    I'm concerned about this query:
    PHP Code:
    "SELECT * FROM $doen1
    The potential problem is if somehow someone gets a value into the database for $doen1 that is not a table name, but instead something destructive, such as:
    Code:
    (DROP DATABASE dbname) AS foo
    (Not sure if that particular example would work in terms of doing nasty things to your database, but you get the idea.)

    So, if you're not 100% sure that the value returned from that query will always be a valid table name and nothing else, then you need to sanitize it. If we assume that your table names will always consist only of letters, digits, and/or underscores, you could test it before using it with something like:
    PHP Code:
    if(!preg_match('/^\w+$/'$doen1)) {
        throw new 
    Exception("Invalid table name '$doen1'");

    As I alluded to earlier, though, dynamically choosing what table name to use usually tends to indicate a database design that probably is using multiple tables where it should instead be using one table with one or more relationship columns you would instead use in a WHERE clause to limit which rows are accessed from it.
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

  6. #6
    Join Date
    Aug 2017
    Posts
    13
    Thank you so mutch for taking the time to explain everything very clear, time for me to learn a thing or two about database security.

    Thanks again sir.

  7. #7
    Join Date
    Apr 2017
    Posts
    67
    You should probably start with learning "Database Normalization". The fact that you are using incremental column names points to you trying to use the database like a spreadsheet. Never ever put variables in a query. You need to be using "Prepared Statements". It also appears you are storing database table names in the DB which is also no good. Additionally, do not "SELECT *". Specify the column names you want.

  8. #8
    Join Date
    Aug 2017
    Posts
    13
    I am new to database and I started the wrong way so I have to start over.
    Thanks for the info.

  9. #9
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    22,236
    Quote Originally Posted by benanamen View Post
    You should probably start with learning "Database Normalization". The fact that you are using incremental column names points to you trying to use the database like a spreadsheet. Never ever put variables in a query. You need to be using "Prepared Statements". It also appears you are storing database table names in the DB which is also no good. Additionally, do not "SELECT *". Specify the column names you want.
    While I 100% agree with prepared statement usage, in this particular case it wouldn't help, as you can only use place-holders with bound parameters for values, not for identifiers -- which is just another indication that this DB approach is less than optimal.
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

  10. #10
    Join Date
    Nov 2015
    Location
    USA New York
    Posts
    5
    O cool, good PDO Tutorial

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center