Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    33
    Rep Power
    1

    Trouble acessing PDO $stmt array index


    PHP Code:
    <?php
    // post_view.php
    require 'connection.php';
    $id =$conn->lastInsertId('id');
    $stmt $conn->prepare('SELECT * FROM posts WHERE id = LAST_INSERT_ID(id) LIMIT 1');
    $stmt->execute(array('id' => 'LAST_INSERT_ID(id)'));
     while(
    $row $stmt->fetchAll(PDO::FETCH_ASSOC)) {
        
    //$row is an array object? that has one array in index 0
        
    print_r($row);
    echo 
    '<h2>'.$row[':title'].'</h2>';
    //echo '<em>Posted '.date('F j<\s\up>S</\s\up>, Y', $row['date']).'</em><br/>';
    echo nl2br($row[':body']).'<br/>';
    echo 
    '<a href="post_edit.php?id='.$id.'">Edit</a> | <a href="post_delete.php?id='.'id'.'">Delete</a> | <a href="index.php">View All</a>';
    echo 
    '<hr/>';
    }
    It all goes well right up untill i have to echo $row [':title'] and $row['body'], by using print_r($row); i've established that the row returned is an array object? with another array(only one, there is currently only one field in the database )but i cannot acess the contents in the array within the array? any ideas.

    <I LOVE CODE>
    <I LOVE PHP>
    <THE BEAUTIFUL MARRIAGE OF <SCRIPT/> AND <?PHP ?> >
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,068
    Rep Power
    9398
    No colon. Those are only for when you use prepared statements and you name the placeholders like ":title" and ":body" (which is traditional with PDO).
    PHP Code:
    echo '<h2>'.$row['title'].'</h2>'
    Your print_r() statement should have shown you that the elements in the array were only named "title" and "body".
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    33
    Rep Power
    1
    Originally Posted by requinix
    No colon. Those are only for when you use prepared statements and you name the placeholders like ":title" and ":body" (which is traditional with PDO).
    PHP Code:
    echo '<h2>'.$row['title'].'</h2>'
    Your print_r() statement should have shown you that the elements in the array were only named "title" and "body".
    unfortunately I'd tried it that way too unsucessfully(i removed the colon)
    So i tried this

    PHP Code:
    $result $stmt->setFetchMode(PDO::FETCH_NUM);
    while (
    $row $stmt->fetch()) {
     ;

        print 
    '<b>'$row[1].'</b>' "<br/>" $row[2] . "<br/>";
      } 
    now to create a post_edit page...
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    The insert ID part makes no sense.

    First of all, what are you trying to do? You fetch the last ID in PHP and pass it to MySQL and then again fetch the last ID in MySQL itself. That's obviously redundant.

    Secondly, how is PDO supposed to recognize the "id" parameter in the prepared statement? Even I can't tell which of the "id"s is meant to be a parameter. The first? The second? Both? In fact, you have no parameter at all. It's a constant query (the execute stuff is simply ignored). If you want a parameter, it must start with a colon.

    Third, you can't pass SQL expression to a prepared statement, only constant values. That's the whole point. The purpose of prepared statements is to safely pass values to a query without the risk of anybody injecting SQL statements.

    Fourth, the "id" argument you're passing to lastInsertId() and LAST_INSERT_ID() doesn't work like you think it works. Passing an argument to lastInsertId() is completely useless in your case, because MySQL doesn't support that. It's for database systems that have sequences. And passing an argument to LAST_INSERT_ID() does not fetch the ID from that column. It sets the value for the next function call. See the manual or just leave it out.

    Last but not least, you must escape every value before you insert it into your HTML document. Otherwise, anybody can manipulate your page, steal the cookies from your users etc. See The 6 worst sins of security.

    In fact, I already showed you last time how to do it (with an html_escape() function), but you appearently missed that.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    33
    Rep Power
    1
    Yes my code is horrible, however i didn't mean to post this part of the code.
    PHP Code:
    $id =$conn->lastInsertId('id'); 
    i had tried using PDO::lastInsertId, to retrieve the ID for the last post submitted from my database and that didn't work and have since removed it.
    When i use the SQL -> LAST_INSERT_ID it did retrieve the last id and made my code work
    (and yes i read the manual and realized that its a function that returns the id and therefore doesn't need a parameter passed in it.)

    If i understand you correctly this->
    PHP Code:
    $stmt $conn->prepare('SELECT * FROM posts WHERE id = LAST_INSERT_ID(id) LIMIT 1'); 
    should be->
    PHP Code:
    $stmt $conn->prepare('SELECT * FROM posts WHERE id = :id LIMIT 1'); 
    then execute it as->
    PHP Code:
    $stmt->execute(array(':id' => 'LAST_INSERT_ID(id)')); 
    because i still want it to retrieve the id for the last post

    Lastly, im guesing this function

    PHP Code:
     <?php function html_escape($input) { 
        return 
    htmlspecialchars($inputENT_COMPAT ENT_HTML401'utf-8'); 
    }
    is for cleaning up my $_POST values?? so is $input=$_POST??
    I always appreciate your help @Jacques1
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    33
    Rep Power
    1
    PHP Code:
    <?
    require 'connection.php';//the html_escape fn is here


    echo html_escape<<<HTML
    <form method="post" id ="myform">
        <table>
            <tr>
                <td><label for="title">Title</label></td>
                <td><input name="title" id="title" /></td>
            </tr>
            <tr>
                <td><label for="body">Body</label></td>
                <td><textarea name="body" id="body"></textarea></td>
            </tr>
            <tr>
                <td></td>
                <td><input type="submit" value="Post" /></td>
            </tr>
        </table>
        </form>
        


    </body>
    </html>
  12. #7
  13. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by rhodoscoder
    then execute it as->
    PHP Code:
    $stmt->execute(array(':id' => 'LAST_INSERT_ID(id)')); 
    No, you don't understand. You cannot insert SQL commands with prepared statements. They've been invented to prevent this. The whole purpose of prepared statements is to not allow what you're trying to do.

    You know that in the old days, people used to insert values directly into the query string, right? I'm talking about stuff like this:

    PHP Code:
    <?php

    // insecure code! don't use this!

    $insecure_query '
        SELECT
            user_id
        FROM
            users
        WHERE
            email = "' 
    $_POST['email'] . '" AND password = "' $_POST['password'] . '"

    '
    ;
    This is a gigantic security risk, because it allows anybody to inject their own SQL commands through the email or password parameter (called SQL injection). For example, I could log in as an administrator simply by manipulating the query.

    To prevent this, smart people have invented prepared statements. The idea is to separate the query itself from the values that should be inserted into the query. You define the query. And your users may only set the values. So they have no chance of changing the query itself.

    Technically, it works like this: You create a "query template" with placeholders for values and send it to the database system. MySQL parses this template, comes up with an execution plan etc. Then you assign the values to the placeholders and pass them to MySQL. A value can be a string or an integer or a boolean or whatever. Finally, MySQL can execute the statement like a normal query.

    So after the query template has been defined, you can only pass constant values to MySQL. You cannot insert SQL commands into the query. That's the whole purpose of prepared statements. That's what makes them secure.

    If you could insert SQL commands like LAST_INSERT_ID(), then what's the whole point of using a prepared statement? We'd be back at the insecure code above.



    Originally Posted by rhodoscoder
    Lastly, im guesing this function [...] is for cleaning up my $_POST values??
    No. It's for every value you want to echo or insert into the HTML document. It doesn't matter if that value comes from $_GET, $_POST, the database or whatever.

    You must not echo raw values, because this enables your visitors to manipulate your page. Take this example:

    PHP Code:
    <?php

    // insecure code! don't use this!

    $page_id $_GET['page'];
    echo 
    $page_id;
    This allows me to put anything I want into your page simply by passing it through the page parameter. For example, I could insert a <script> element that steals your cookies and sends them to my server. Or I could put a download link with malware on your site.

    To prevent this, every value must be escaped before you output it. Escaping means that the values will be interpreted as literal text rather than HTML markup.

    Read "The 6 worst sins of security" for further explanations and examples (the link is in my previous reply and my signature).
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    33
    Rep Power
    1
    So now i have two problems, this doesn't work->
    PHP Code:
    <?php
    require_once 'functions.php';
    // post_add.php
       
    if ( isset ($_POST['title']) && ($_POST['body']) &&  !empty($_POST) ){ 
        require 
    'connection.php';
        if(
    html_escape($_POST))
        
    $stmt $conn->prepare('INSERT INTO posts (title,body) VALUES (:title, :body )');
        
        
    $stmt->bindValue(':title'$_POST['title']);
        
    $stmt->bindValue(':body'$_POST['body']);
       
        
    $stmt->execute();
       
        echo 
    'Entry posted. <a href="post_view.php?id='.html_escape($database->lastInsertId()).'">View</a>';
       
    }
    i have the fn in functions.php and i get this error
    Call to undefined function html_escape()

    secondly with this code ->
    PHP Code:
    <?php
    // post_view.php
    require 'connection.php';
    $stmt $conn->prepare('SELECT * FROM posts WHERE id = :id LIMIT 1');
    //i didn't pass the
      

    $stmt->execute(array(':d' => ':id'));
    i cannot retrieve the last post id.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    33
    Rep Power
    1
    However when i tried this it worked
    PHP Code:
    .htmlspecialchars($conn->lastInsertId(), ENT_COMPAT ENT_HTML401'utf-8'
    But i know i can't do that all throughout the code and thats why you used the html_escape function.I just know i made another dumb mistake and the functions.php cannot be accessed
  18. #10
  19. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by rhodoscoder
    i have the fn in functions.php and i get this error
    Call to undefined function html_escape()
    Well, obviously the function doesn't get defined in the functions.php that PHP includes.

    Double check that you're including the right functions.php (put an echo into it) and make sure that the function definition exists and actually gets executed.

    In general, using relative paths for require is a very bad idea, because PHP will search the file in all kinds of places: the include_path, the working directory (whatever that is) and the script that has the require statement. You never know what you get.

    So it's strongly recommended that you use absolute paths. To get the path of the calling script, use dirname(__FILE__) or simply __DIR__ (in PHP ≥ 5.4). Then you can set the path relative to that:

    PHP Code:
    <?php

    require_once __DIR__ '/functions.php';
    There are other issues with your code:

    The !empty($_POST) is superfluous, because if $_POST['title'] is set, then you already know that $_POST is not empty. Checking it again is redudant. It's like saying "You have to be at least 18 and older than 17". And isset ($_POST['title']) && ($_POST['body']) can be shorthened to !empty($_POST['title']).

    The if(html_escape($_POST)) makes no sense at all. I don't even know what that is supposed to do. The html_escape($database->lastInsertId()) is correct, though. That's what the function is for: escaping values before you output them.



    Originally Posted by rhodoscoder
    PHP Code:
    <?php

    $stmt
    ->execute(array(':d' => ':id'));
    I don't know what that is supposed to do. You have no ":d" parameter in your statement. And the ":id" string would be passed literally to the statement. It does not get evaluated in any way (like I already said multiple times).

    You can only pass values to a prepared statement. Nothing else. No SQL commands or other parameters or whatever.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    33
    Rep Power
    1
    Ok for now i have included the function at the top of each file because for the life of me i cannot figure this out.
    PHP Code:
    require_once __DIR__ 
    Secondly someone else had told me to drop
    PHP Code:
    !empty($_POST
    and why would i listen. I used your recommended code and voila and I also have javascript validating the fields so nothing would ever be submitted to the database if the fields were blank.
    I spent the last two hours reading up on XSS attacks and understand why htmlspecialchar() is necessary, and it was clearly not necessary here->
    Then follows dumb code,
    PHP Code:
    if(html_escape($_POST)) 

    and this was a dumb error too
    PHP Code:
    $stmt->execute(array(':d' => ':id')); 
    it is now->
    PHP Code:
    $stmt->execute(array('id' => ':id')); 
  22. #12
  23. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by rhodoscoder
    PHP Code:
    $stmt->execute(array('id' => ':id')); 
    What's the ":id" doing there? That doesn't look like a number to me.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    33
    Rep Power
    1
    Originally Posted by Jacques1
    What's the ":id" doing there? That doesn't look like a number to me.
    i cant just put one number there , the id changes right after a post is submitted to the database, the id is from an auto increment primary ID value
  26. #14
  27. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by rhodoscoder
    i cant just put one number there , the id changes right after a post is submitted to the database, the id is from an auto increment primary ID value
    I'll try to explain it one last time: You can only pass values to a prepared statement. You can pass the number 123. Or a variable containing a number. But you cannot, I repeat, you cannot pass anything else. Not sure why this is so hard to understand.

    Your code

    PHP Code:
    $stmt->execute(array('id' => ':id')); 
    makes no sense whatsoever. What this does is insert the literal string ":id" into the query, which will obviously blow up MySQL. Didn't you get a big error message?

    So where is the last ID you wanna pass to the statement? Let's say it's in the variable $insert_id. Then you'd do this:

    PHP Code:
    $stmt->execute(array('id' => $insert_id)); 
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    33
    Rep Power
    1
    Ok so like i did it here(in my insert prepared statement)->
    PHP Code:
    <?php  

       
    if ( isset ($_POST['title']) && ($_POST['body']) &&  !empty($_POST) ){  
        require 
    'connection.php'
        if(
    html_escape($_POST)) 
        
    $stmt $conn->prepare('INSERT INTO posts (title,body) VALUES (:title, :body )'); 
         
        
    $stmt->bindValue(':title'$_POST['title']); 
        
    $stmt->bindValue(':body'$_POST['body']); 
        
        
    $stmt->execute();
    so then could i do
    PHP Code:
    $insert_id=$_GET['id'];
     
    $stmt->execute(array('id' => $insert_id)); 
    and i actually tried it and it worked .I'm such an idiot(but you know that already). I really appreciate your help though, thanks!!
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo