#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    9
    Rep Power
    0

    Problem with PDO insert script


    When i Run the script it displays this erro i di not know what to do
    ERROR: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'chapter=sdasd' at line 1

    this is the script
    if (isset($_POST['chapter'])AND isset($_POST['verse']))
    {
    try {
    $pdo_options[PDO::ATTR_ERRMODE]=PDO::ERRMODE_EXCEPTION ;
    $Dbc= new PDO('mysql:host=localhost;dbname=gths','fruanthony','admin',$pdo_options);
    $message=$Dbc->query("SELECT info FROM bible where verse=".$_POST['verse']."AND chapter=".$_POST['chapter'] );
    while($results = $message->fetch()){
    $results['info'];
    }
    }
    catch(Exception $e)
    {die ('ERROR: '.$e->getMessage());
    exit;
    }
    }
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

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

    Do not insert raw values into query strings.

    You should be glad that you stumbled upon this error and didn't have to learn it "the hard way" with some script kiddie f*cking up your database.

    PDO has a great feature called "prepared statement". That's what you should use (see the link above). Don't just inject some POST parameters into the query string, because this tears a big security hole in your application -- the quotes you accidentally inserted into the query might as well have been malicious SQL purposely injected by an attacker.

    And I hope "admin" isn't your actual database password?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Location
    Sydney Australia
    Posts
    183
    Rep Power
    84
    Originally Posted by fruanthony
    "SELECT info FROM bible where verse=".$_POST['verse']."AND chapter=".$_POST['chapter'] );
    And this will expand to something like

    Code:
    SELECT info FROM bible where verse=3AND chapter=16 );
    because of the missing space before AND, and the parser doesn't know what to make of the term after 3AND so you get the error pointing to the code right after the error.

    But like Jacques said, don't do this . It's a BIG SECURITY HOLE. Use the prepared statements available in PDO and stay safe.
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    119
    Rep Power
    33
    You are also missing your closing quote mark at the end of your query
    PHP Code:
    "SELECT info FROM bible where verse=".$_POST['verse']." AND chapter=".$_POST['chapter']."); 
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    9
    Rep Power
    0
    i have updated the query but it is does not display anything
    if (isset($_POST['chapter'])AND isset($_POST['verse']))
    {
    try {
    $pdo_options[PDO::ATTR_ERRMODE]=PDO::ERRMODE_EXCEPTION ;
    $Dbc= new PDO('mysql:host=localhost;dbname=gths','fruanthony','admin',$pdo_options);
    $message=$Dbc->prepare("SELECT info FROM bible where verse= ? " );
    $message->execute(array(
    $_POST['verse']

    ));
    while($results = $message->fetch()){
    $results['info'];
    //var_dump($results);
    echo "<br>";
    }
    //var_dump($results);
    }
    catch(Exception $e)
    {die ('ERROR: '.$e->getMessage());
    }
    }
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    What is this supposed to display when you don't echo anything except line breaks?

    By the way, you should remove this "try-catch" stuff. It's completely useless, because what you're doing there is what the exception would do, anyway. And it's generally a bad idea to display internal error messages.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    9
    Rep Power
    0
    Originally Posted by Jacques1
    What is this supposed to display when you don't echo anything except line breaks?

    By the way, you should remove this "try-catch" stuff. It's completely useless, because what you're doing there is what the exception would do, anyway. And it's generally a bad idea to display internal error messages.
    i thought
    this was what was going to echo the resulut
    <echo $a['info'];>
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    74
    Rep Power
    18
    Originally Posted by fruanthony
    i thought
    this was what was going to echo the resulut
    <echo $a['info'];>
    True, but you are not echoing anything in the loop.

    Instead of keep calling fetch() you can also use fetchAll(), and iterate over that resulset
  16. #9
  17. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by Aurum84
    Instead of keep calling fetch() you can also use fetchAll(), and iterate over that resulset
    You shouldn't call any "fetch" method at all unless you actually physically need the rows in an array.

    Simply loop over the statement object, it's iterable:
    PHP Code:
    $message->execute();
    foreach (
    $message as $row) {
        ...



    @ fruanthony:

    We need your full code. In the snippet above, there is no "$a".
    Last edited by Jacques1; February 7th, 2013 at 06:16 AM.
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    9
    Rep Power
    0
    Originally Posted by Jacques1
    You shouldn't call any "fetch" method at all unless you actually physically need the rows in an array.

    Simply loop over the statement object, it's iterable:
    PHP Code:
    $message->execute();
    foreach (
    $message as $row) {
        ...



    @ fruanthony:

    We need your full code. In the snippet above, there is no "$a".
    thata is all about the code the only thing i have omitted is the php open and close tags
    what i want to do is to display the result of that query could u help me????
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    9
    Rep Power
    0
    thata is all about the code the only thing i have omitted is the php open and close tags
    what i want to do is to display the result of that query could u help me??
  22. #12
  23. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    I asked you to post the full code you currently use, because the second code snippet makes absolutely no sense given the first snippet. What is "$a"? There is no "$a" in the first code. Maybe you mean $results? I don't know, so please write down the code. That shouldn't be too hard.

    What's also weird is that sometimes you just write down an expression like $results['info'] without doing anything with it. That has no effect at all. It's like writing down "1 + 1;" in a single line. You have to actually output that like in
    PHP Code:
    echo 'Hallo world!'

IMN logo majestic logo threadwatch logo seochat tools logo