PHP PDO and Mysql

Question

<?php
// include to get database connection
include_once 'config/db.php';

try{
    $a_id = "SELECT a.id FROM aluno a, utilizador u WHERE a.utilizador_id = u.id
    AND u.nome =" . $_POST['nome'];
    $prof = 1; 

    $query = "INSERT INTO classificacao(nota, semestre, dt_classif, 
    aluno_id, utilizador_id) VALUES (nota=:nota, semestre=:semestre, dt_classif=DEFAULT ,
    aluno_id=:aluno_id, utilizador_id=:utilizador_id)";

    $stmt = $con->prepare($query);

    $stmt->bindParam(":nota", $_POST['nota']);
    $stmt->bindParam(":semestre", $_POST['semestre']);
    $stmt->bindParam(":aluno_id", $a_id);
    $stmt->bindParam(":utilizador_id", $prof);

    // execute the query
    if($stmt->execute()){
        echo "Product was created.";
    }else{
        echo "Unable to create product.";
    }
}
catch(PDOException $exception){
    echo "Error: " . $exception->getMessage();
}

?>

I'm creating a CRUD with jQuery and PHP and I'm almost sure the error is in this file, I cannot create data, and the echos "Product was created." and "Unabled to create product." are not displaying anywhere. I was wondering if you could help

How should we know? You don't say what is wrong? Are you getting an error? "Something wrong" is not telling anything. — Rasclatt, Jun 17 '15 at 02:58
please read the manuals http://php.net/pdo.prepared-statements and http://php.net/manual/en/pdo.error-handling.php and your query is all wrong. — Funk Forty Niner, Jun 17 '15 at 03:01
What are you trying to do with `$a_id`? Store the query in your DB?? — chris85, Jun 17 '15 at 03:06
add `$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` right after the connection is opened, and you'll see what I mean. — Funk Forty Niner, Jun 17 '15 at 03:08
@chris85 No, thats $query role. I'm trying to select the "ID" from a table, however the user sends a name(stored in a referenced table) and not the "ID" i need. Not sure if i made myself clear enough — RUIFERNANDE5, Jun 17 '15 at 03:10
Issue one you don't execute that query. Issue two that query is open to injections. (potential third issue is if `nome` is a string it should be quoted). A later issue in your code is your `insert` syntax, you're mixing the `update` syntax with the `insert` syntax. http://dev.mysql.com/doc/refman/5.6/en/insert.html — chris85, Jun 17 '15 at 03:11
@chris85 so if instead of "SELECT a.id FROM aluno a, utilizador u WHERE a.utilizador_id = u.id AND u.nome =" . $_POST['nome'];" / "SELECT a.id FROM aluno a, utilizador u WHERE a.utilizador_id = u.id AND u.nome = '$_POST['nome']' "; i'll make it safer? — RUIFERNANDE5, Jun 17 '15 at 03:18
No, it would work but not safe. User input should be separated from the query. Bind it like you do for the insert. Here's a thread on injection prevention, http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1. — chris85, Jun 17 '15 at 03:18
@chris85 How am i mixing update with insert? Can't relate to that and if i am not executing the query what is prepare and $stmt->execute() doing? Thank you about the thread I'll definitely check it out — RUIFERNANDE5, Jun 17 '15 at 03:23

score 0 · Accepted Answer · edited May 23 '17 at 12:06

Here's a rough answer which should resolve the injection issue, the non-executing query issue, and the incorrect insert syntax. I don't have your DB so I can't confirm this is fully functional but it should be closer..

<?php
// include to get database connection
include_once 'config/db.php';
try{
    $a_id = "SELECT a.id as aid FROM aluno a, utilizador u WHERE a.utilizador_id = u.id
    AND u.nome = ?";
    $stmt = $con->prepare($query);
    $stmt->execute(array($_POST['nome']));
    while ($row = $stmt->fetch_assoc()) {
        $aids[] = $row['aid'];
        $a_id = $row['aid'];
    }
    // what are you doing if there are more than one record?
    // current execution will only have the last id as $a_id
    $prof = 1; 
    $query = "INSERT INTO classificacao(nota, semestre, aluno_id, utilizador_id) 
                VALUES (?, ?, ?, ?)";
    $stmt = $con->prepare($query);
    // execute the query
    if($stmt->execute(array($_POST['nota'], $_POST['semestre'], $a_id, $prof))){
        echo "Product was created.";
    }else{
        echo "Unable to create product.";
    }
}
catch(PDOException $exception){
    echo "Error: " . $exception->getMessage();
}
?>

Notice the insert syntax used here. At the start you define the columns

INSERT INTO classificacao(nota, semestre, aluno_id, utilizador_id)

Then you pass the values in after the values. Each value is separated by a comma.

VALUES (?, ?, ?, ?)

You don't pass the columns in there. The ? are placeholders for the values going to the DB.

Links for further reading:

How can I prevent SQL injection in PHP?
http://dev.mysql.com/doc/refman/5.6/en/insert.html
https://dev.mysql.com/doc/refman/5.0/en/update.html
http://php.net/manual/en/pdo.prepared-statements.php

Thanks a lot mate, solved my problem, and I'll definitely check it out! — RUIFERNANDE5, Jun 17 '15 at 03:45

PHP PDO and Mysql

1 Answers1