What is the error in querying this?

Question

What's wrong on that code? -- It's keep refusing to create account.

$query = "
    INSERT INTO `accounts`(`username`, `dispname`, `email`, `password`, `type`, `blocked`, `points`)
    VALUES ($disname,$username,$email,$password,1,false,0)";        
$result = mysql_query($query);
if($result){
    $Registered = "You have registered successfully.";
} else {
    $ERROREMAIL = "There Were an Error Registering your email, please contact our support.";    
}

I am totally confused.

By the way this is the structure of the database, hopefully someone helps.

CREATE TABLE `accounts` (
  `id` int(11) NOT NULL,
  `username` varchar(255) DEFAULT NULL,
  `dispname` varchar(255) NOT NULL DEFAULT 'someone',
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `type` int(10) NOT NULL,
  `blocked` tinyint(1) NOT NULL DEFAULT '0',
  `points` int(10) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

ALTER TABLE `accounts`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`);

ALTER TABLE `accounts`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2;

Missing the `quotes` for values. `VALUES ('$disname','$username','$email','$......` — Sougata Bose, Dec 23 '15 at 12:24
It doesn't seem to matter how many times we repeat the words "MySQL deprecated" or "Switch to MySQLi or PDO and use prepared statements/bind variables", nobody ever seems to listen — Mark Baker, Dec 23 '15 at 12:33
Then listen.... stop using the old MySQL extension (which isn't even available in the latest versions of PHP), and switch to using MySQLi or PDO; use prepared statements with bind variables; and you'll not only eliminate the need to escape your string values and reduce the risk of SQL injection against your database, you won't need to remember to quote strings in SQL statements either — Mark Baker, Dec 23 '15 at 12:40
$connection = mysqli_connect($DBHOST, $DBUSER, $DBPASS, $DBNAME); also mysqli_query($connection, $query); — Slavi, Dec 23 '15 at 12:43
[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Dec 23 '15 at 12:45
Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Dec 23 '15 at 12:45
Please use PHP's [built-in functions](http://jayblanchard.net/proper_password_hashing_with_PHP.html) to handle password security. If you're using a PHP version less than 5.5 you can use the `password_hash()` [compatibility pack](https://github.com/ircmaxell/password_compat). — Jay Blanchard, Dec 23 '15 at 12:46
Add error reporting to the top of your file(s) right after your opening ` — Jay Blanchard, Dec 23 '15 at 12:47
Although change to Mysqli or PDO might take more change, but in long term its worth it. At least it wont bite back in the nearest future. — Andrew, Dec 23 '15 at 12:48

devpro · Answer 1 · 2015-12-23T12:37:00.323

2

Try this Query:

$query = "INSERT INTO `accounts` 
(`username`, `dispname`, `email`, `password`, `type`, `blocked`, `points`) 
VALUES ('$disname','$username','$email','$password',1,0,0)";

What change?

Adding single quote on string variables.
Also changed the value of blocked column as 0.

edited Dec 23 '15 at 12:37

answered Dec 23 '15 at 12:31

devpro

16,184
3
27
38

already added, but still recieving error. – Slavi Dec 23 '15 at 12:34
Also i found that `blocked` tinyint(1) NOT NULL DEFAULT '0', so it would be 0 not false. But also didn't solve – Slavi Dec 23 '15 at 12:36
answer updated, use 0 instead of false in blocked column – devpro Dec 23 '15 at 12:37
no yet progress, still the same problem. – Slavi Dec 23 '15 at 12:39
@Ahmed: you need to change the data type of blocked column as tinyint to int if you want to set 0 as Default. – devpro Dec 23 '15 at 12:41
and in both cases its working fine.. can you share the MYSQL error? – devpro Dec 23 '15 at 12:44
In this case, i would like to have your skype, or something to talk in private. – Slavi Dec 23 '15 at 12:46
i think SO also provide the chat conversation option. any way your skype id? – devpro Dec 23 '15 at 12:47

score 1 · Accepted Answer · edited May 23 '17 at 12:33

OP's comment:

"$connection = mysqli_connect($DBHOST, $DBUSER, $DBPASS, $DBNAME); also mysqli_query($connection, $query); – Ahmed Alaa 1 hour ago"

2 things wrong here. You're connecting with mysqli_ then querying with mysql_.

$result = mysql_query($query);

which should read as

$result = mysqli_query($connection, $query);

Those different APIs do NOT intermix. You must use the same one from connecting to querying.

Can I mix MySQL APIs in PHP?

Then, missing quotes around your variables (for strings) in values.

VALUES ($disname,$username,$email,$password,1,false,0)";

which should read as:

('$disname','$username','$email','$password',1,false,0)

But that leaves you open to SQL injection.

Use mysqli_* with prepared statements, or PDO with prepared statements.

Consult these following links http://php.net/manual/en/mysqli.error.php and http://php.net/manual/en/function.error-reporting.php and apply that to your code during testing.

This does not help you during testing in order to get the real error(s):

else {
    $ERROREMAIL = "There Were an Error Registering your email, please contact our support.";
}

This will:

else {
    echo "Error: " . mysqli_error($connection);
}

You can set it back to your original method once there are no more errors.

Now i figured where was the mistake but i have used PDO now. But if it's safer to use mysqli, i will get back there. — Slavi, Dec 23 '15 at 14:12
@AhmedAlaa Either mysqli or PDO, the important thing here, is to use a "prepared statement". — Funk Forty Niner, Dec 23 '15 at 14:13
great then, and actually 'blocked' row is not either a string or integer, it's boolean. — Slavi, Dec 23 '15 at 14:14

score 0 · Answer 3 · answered Dec 23 '15 at 12:59

0

You need to pass strings as strings not as integers

Try this:

$query = "
INSERT INTO `accounts`(`username`, `dispname`, `email`, `password`, `type`, `blocked`, `points`)
VALUES ('$disname','$username','$email','$password',1,false,0)";

answered Dec 23 '15 at 12:59

Danyal Sandeelo

12,196
10
47
78

score 0 · Answer 4 · answered Dec 23 '15 at 13:59

0

Thanks for you all.

Solution is as it is.

Updating your php version to PHp 7_0_0. Updating phpmyadmin to 4_5_2. Updating MySql to 5_6_27.

Update the usage of old mysql -> PDO.

answered Dec 23 '15 at 13:59

Slavi

576
1
3
18

What is the error in querying this?

4 Answers4