Can't insert into a database with PHP

Question

For learning purposes, I am creating a password manager on my local system. However there is a problem when it comes to inserting data into the database and I'm not sure why it isn't working.

My entire document can be the found below.

<?php

    $user = 'root';
    $pass = '';
    $db   = 'accounts';
    $server = 'localhost';

    $db_handle = mysql_connect($server, $user, $pass);

    if (!$db_handle) {
        echo "Unable to connect to DB: " . mysql_error();
        exit;
    }

    $db_found = mysql_select_db($db, $db_handle);

    if ($db_found) {

        if (isset($_POST['type'])) {
            $getOrSet = $_POST['type'];
            $site = $_POST['site'];
            $login = $_POST['login'];
            if ($getOrSet == 'get') {
                $pass = mysql_fetch_assoc(mysql_query("SELECT password FROM manager WHERE site = '$site' AND username = '$login'"))['password'];
            } else if ($getOrSet == 'set') {
                $url = $_POST['url'];
                $pass = $_POST['pass'];
                mysql_query("INSERT INTO manager (site, url, username, password) VALUES ('$site', '$url', '$login' '$pass')");
            }
        }

    } else {
        echo "Unable to select database: " . mysql_error();
    }
    mysql_close($db_handle);

?>

<!doctype html>
<html lang="en">
    <head>
        <meta charset="UTF-8">
        <title>Document</title>
        <style>
            #left,#right{width:50%;margin:0;padding:0;float:left;text-align:center}
            form{width:300px;margin:0 auto}
            label{width:300px;display:block;line-height:1.65em}
            input{float:right}
            #pass{width:300px}
            #pass>span{float:right}
        </style>
    </head>
    <body>
        <h1>Password Manager</h1>
        <div id="left">
            <h2>Get Password</h2>
            <form action="index.php" method="post">
                <label>Site: <input type="text" name="site" /></label>
                <label>Email/Username: <input type="text" name="login" /></label>
                <input type="hidden" name="type" value="get" />
                <?php if(isset($_POST['type'])){if ($getOrSet == 'get') {echo "<span id=\"pass\">Password: <span>$pass</span></span>";}} ?>
                <input type="submit" value="Submit" />
            </form>
        </div>
        <div id="right">
            <h2>Set Password</h2>
            <form action="index.php" method="post">
                <label>Site: <input type="text" name="site" /></label>
                <label>URL: <input type="text" name="url" /></label>
                <label>Email/Username: <input type="text" name="login" /></label>
                <label>Password: <input type="password" name="pass" /></label>
                <input type="hidden" name="type" value="set" />
                <input type="submit" value="Submit" />
            </form>
        </div>
    </body>
</html>

Can someone please tell me why this code doesn't work?

mysql_query("INSERT INTO manager (site, url, username, password) VALUES ('$site', '$url', '$login' '$pass')");

[Please, don't use `mysql_*` functions](http://stackoverflow.com/q/12859942/1190388) in new code. They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about prepared statements instead, and use [tag:PDO] or [tag:MySQLi]. — hjpotter92, Aug 08 '14 at 11:05
append `or die(mysql_error())` like `mysql_query($sql) or die(mysql_error());` and see what error comes — Satish Sharma, Aug 08 '14 at 11:06
@hjpotter92 As this is just for me, I couldn't care less if they are deprecated or not, Just as long as they work. — Spedwards, Aug 08 '14 at 11:07
@SKRocks I get this error, "Column count doesn't match value count at row 1". There are only 4 columns in the table so I don't exactly understand what this means. — Spedwards, Aug 08 '14 at 11:10
@Spedwards - you mentioned you're learning. Is there any particular tutorial that you're following? If yes, could you post the link? — N.B., Aug 08 '14 at 11:11
@N.B. No specific tutorial. And I'm not exactly learning in any particular order either. — Spedwards, Aug 08 '14 at 11:12
@Spedwards use escapes i think you are trying to insert date with "," or etct. try my answer — Satish Sharma, Aug 08 '14 at 11:15
There are many bad things you've done with that code. The reason people tell you that you shouldn't use deprecated functions is not because someone decided to create new thing that only hipsters will use. The newer mechanisms (PDO) actually lets you write LESS code that's instantly secure against any possible SQL injections (note that SQL injection isn't always about an attack, lots of sql injections produce problems in regular algorithm flow). You also haven't showed us what the input you used was, so just by looking at the code - no one can tell you what's wrong. — N.B., Aug 08 '14 at 11:16
So to conclude that long comment, show us the input you've used to perform those queries. It appears one of your inputs has `'` character inside it, causing the query to fail in some way. — N.B., Aug 08 '14 at 11:16
Well the "Get Password" section works and my inputs for "Set" are: Gmail, http://gmail.com, myemail@gmail.com, myAlphaNumericPass. So there an no `'`s — Spedwards, Aug 08 '14 at 11:21
@N.B. It wasn't a `'` getting added but a missing `,` after `'$login'` — Spedwards, Aug 08 '14 at 11:25
I'm sure the answer to the following question will depend on the person but which is better to use, mysqli or PDO? — Spedwards, Aug 08 '14 at 11:28
@Spedwards - PDO, hands down. It's got nicer interface. Naturally, this is personal opinion. — N.B., Aug 08 '14 at 11:37

Satish Sharma · Answer 1 · 2014-08-08T11:45:34.553

3

use escapes before insert like

$site = mysql_real_escape_string($site);
$url = mysql_real_escape_string($url);
$login = mysql_real_escape_string($login);
$pass = mysql_real_escape_string($pass);

// now insert 

mysql_query("INSERT INTO manager (site, url, username, password) VALUES ('$site', '$url', '$login', '$pass')");

Note : mysql_* is deprecated. use mysqli_* or PDO

edited Aug 08 '14 at 11:45

answered Aug 08 '14 at 11:14

Satish Sharma

9,547
6
29
51

@hd. oh sorry bro typing mistake – Satish Sharma Aug 08 '14 at 11:18
I tried this just before you posted and doesn't work. Though I did `$site = mysql_real_escape_string($_POST['site']);` but I don't see any real difference. – Spedwards Aug 08 '14 at 11:18
@Spedwards Then look at the documentation as to what mres will do. – ʰᵈˑ Aug 08 '14 at 11:19
@Spedwards try my code. then see what happened. i will definitly help u – Satish Sharma Aug 08 '14 at 11:20
Still getting the same error, "Column count doesn't match value count at row 1" – Spedwards Aug 08 '14 at 11:23
Oh wow... Such a small thing but I just noticed there is no comma after `'$login'` – Spedwards Aug 08 '14 at 11:24

score 1 · Answer 2 · answered Aug 08 '14 at 11:29

The problem is that there is a missing , after '$login' so

mysql_query("INSERT INTO manager (site, url, username, password) VALUES ('$site', '$url', '$login' '$pass')");

should have been

mysql_query("INSERT INTO manager (site, url, username, password) VALUES ('$site', '$url', '$login', '$pass')")

Can't insert into a database with PHP

2 Answers2