Can't insert data to mysql using PDO

Question

Please tell me, what am I doing wrong.

<?php
if ( empty( $_POST ) ){
?>
<form name='registration' action='pdo1.php' method='POST'/>
  <input type="text" name="user_name">
  <input type="password" name="password">
  <input type="text" name="email">
  <button type="submit">Submit</button>
</form>
<?php
} else {
    // host, myU, myP and myDB are all correct
    $db_user = 'myU'; 
    $db_pass = 'myP'; 
    $db = new PDO( 'mysql:host=localhost;dbname=myDB', $db_user, $db_pass );

    $sql = "INSERT INTO users ( user_name, password, email ) VALUES ( :username, :password, :email )";

    $query = $db->prepare( $sql );
    $result = $query->execute( array( ':username'=>$username, ':password'=>$password, ':email'=>$email ) );

    if($result) {
        echo "Worked!";
    }
}
?>

When I hit submit, I'm getting blank page, without "Worked!"... And obviously this doesn't insert anything to database.

There are no defined variables. Username, password and email variables are not defined — Rotimi, Apr 17 '17 at 05:58
**WARNING**: Writing your own access control layer is not easy and there are many opportunities to get it severely wrong. Please, do not write your own authentication system when any modern [development framework](http://codegeekz.com/best-php-frameworks-for-developers/) like [Laravel](http://laravel.com/) comes with a robust [authentication system](https://laravel.com/docs/5.4/authentication) built-in. At the absolute least follow [recommended security best practices](http://www.phptherightway.com/#security) and **never store passwords as plain-text**. — tadman, Apr 17 '17 at 05:59
Also if you are executing the insert script on the same page, why define a different action attribute?? — Rotimi, Apr 17 '17 at 06:03
Thank you guys. Akin, I tried to define it as `$username = !empty($_POST['username']) ? trim($_POST['username']) : null;` and same as for password and email, but it's the same blank page unfortunately. — marrysho, Apr 17 '17 at 06:13
Thank you for the links and warning, tadman. This is however only for testing proposes, it's my second day with PHP :) I wanted to start without using any framework — marrysho, Apr 17 '17 at 06:14
Ok, I think I got it, Akin(about `action`). I should use PHP_SELF with escape function - correct? — marrysho, Apr 17 '17 at 06:21
If you have an answer that you think is good, please accept it :-) — bestprogrammerintheworld, Apr 18 '17 at 18:16

score 0 · Answer 1 · answered Apr 17 '17 at 06:04

0

Though its good to use a framework, but try this for learning purposes ,note there is no validation or sanitization done , also you do not need to store the password in plain text , you need to read more about PHP security, this is for learning sake and should never be used in production...

<?php
if ( empty( $_POST ) ){
?>
<form name='registration' action='<?php echo $_SERVER["PHP_SELF"]; ?>' method='POST'/>
  <input type="text" name="user_name">
  <input type="password" name="password">
  <input type="text" name="email">
  <button type="submit">Submit</button>
</form>
<?php
} else {

 //n
 $username = $_POST["user_name"];
 $password = $_POST["password"];

$email= $_POST["email"];
    // host, myU, myP and myDB are all correct
    $db_user = 'myU'; 
    $db_pass = 'myP'; 
    $db = new PDO( 'mysql:host=localhost;dbname=myDB', $db_user, $db_pass );

    $sql = "INSERT INTO users ( user_name, password, email ) VALUES ( :username, :password, :email )";

    $query = $db->prepare( $sql );
    $result = $query->execute( array( ':username'=>$username, ':password'=>$password, ':email'=>$email ) );

    if($result) {
        echo "Worked!";
    }
}
?>

answered Apr 17 '17 at 06:04

Big Zak

1,040
12
17

Thank you, razzbee, but I'm having troubles with this - it's outputting the same blank page for some reason. – marrysho Apr 17 '17 at 06:18
Add this after the php opening tag ini_set('display_errors', true); error_reporting(E_ALL); – Big Zak Apr 17 '17 at 06:21
1

The OP's code is actually quite good from a security point of view. Just because you have a framework you'r not safe. That is misleading. – bestprogrammerintheworld Apr 17 '17 at 06:25
This is probably weird, but I'm still getting the blank page for some reason... I put exactly as you said `ini_set('display_errors', true); error_reporting(E_ALL);` after ` – marrysho Apr 17 '17 at 06:29
I tried to add the `else` for the `$result` and I'm getting this else instead of the result for some reason... So it's not working - but why. ` if($result) { echo "Worked!"; } else { echo "Something wrong!"; }` – marrysho Apr 17 '17 at 06:32
I configured it properly, so now it outputting the proper errors, I think. I'm getting `Column not found: 1054 Unknown column 'user_name'`, doesn't the script should create it by it's own? So I need to create columns "user_name", "password" and "email" relative to my script, right? – marrysho Apr 17 '17 at 06:42

score 0 · Answer 2 · answered Apr 17 '17 at 06:07

0

There is no define variable, Please define it here is the sample code

$db_user = 'root'; 
$db_pass = ''; 
$db = new PDO( 'mysql:host=localhost;dbname=test', $db_user, $db_pass );
//declare
$username = $_POST['user_name'];
$password = $_POST['password'];
$email   = $_POST['email'];

$sql = "INSERT INTO users ( user_name, password, email ) VALUES ( :username, :password, :email )";

$query = $db->prepare( $sql );
$result = $query->execute( array( ':username'=>$username, ':password'=>$password, ':email'=>$email ) );

if($result) {
    echo "Worked!";
}

Please use framework like laravel or any other, that is best and secure way to do

answered Apr 17 '17 at 06:07

Anuj Tiwary

318
1
10

Thanks, but this still doesn't work for some reason – marrysho Apr 17 '17 at 06:22
A framework isn't a solution for getting a good security solution. (That's not the main reason why you use frameworks) – bestprogrammerintheworld Apr 17 '17 at 06:25
I agree with you, the reason i mentioned framework coz there are ready-made module for some basic security solution rather then writing your own custom code which is quick and time saving.hope you are getting my point @bestprogrammerintheworld – Anuj Tiwary Apr 17 '17 at 06:29
The code is fine i guess some syntax error might be there, in order to check please use this commend on your terminal "php yourfillename.php" or please check error_log – Anuj Tiwary Apr 17 '17 at 06:43

score 0 · Answer 3 · answered Apr 17 '17 at 06:22

0

If you have the $result - variable defined like below and don't define the variables $username, $password and $email:

$result = $query->execute( 
    array( ':username'=>$username, 
           ':password'=>$password, 
           ':email'=>$email ) 
);

it will result in:

$result = $query->execute( 
    array( ':username'=>null, 
           ':password'=>null, 
           ':email'=>null ) 
);

and therefore the sql-query would be:

$sql = "INSERT INTO users ( user_name, password, email ) 
VALUES ( null, null, null )";

and above sql-query would obviously fail (if null is not allowed for user_name, password and email in the database-table)

That's why you have to define the actual vales you get from the server, but fetching values from the $_POST-variable.

You need to define

$username = $_POST['user_name'];
$password = $_POST['password'];
$email   = $_POST['email'];

before defining

$result = $query->execute( 
    array( ':username'=>$username, 
           ':password'=>$password, 
           ':email'=>$email ) 
);

answered Apr 17 '17 at 06:22

bestprogrammerintheworld

5,417
7
43
72

Thank you for your answer, but this doesn't work yet as well. I tried to use else on the `result`, and I'm getting the else output. ` if($result) { echo "Worked!"; } else { echo "Something wrong!"; }` Any idea why this can happen, please? – marrysho Apr 17 '17 at 06:33
If you type var_dump ( $result ); what is printed on screen? (the row after $result = $query->execut....) – bestprogrammerintheworld Apr 17 '17 at 06:39
Without `PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION` it doesn't print anything, because there's an error. But with that in mind, I created columns inside my "users" table, before that I thought the script should do that by it self :D Now it says the script is working, but I'm able to insert only the password for some odd reason... I did like following with DB columns inside users table: "user_name", "password", "email". Perhaps I should add something else? – marrysho Apr 17 '17 at 06:52
1

Oh! I think I got it... I should probably use proper type of them... Gonna test it now – marrysho Apr 17 '17 at 06:54
Yes! That was it! Great feeling :D – marrysho Apr 17 '17 at 06:57
Glad to hear that! – bestprogrammerintheworld Apr 17 '17 at 07:23

Can't insert data to mysql using PDO

3 Answers3