Rights INSERT PHPmyadmin MySQL

Question

I have setup a basic table and connected a PHP file with the database. I can fetch data from the database by using SELECT. However, when I try to use UPDATE or INSERT INTO, I get the message:

"INSERT command denied to user ''@'localhost' for table 'table_data'".

When I try to use the query in PMA, i am able to insert data. But when i want to adjust user rights, it says i don't have the rights to do so. But when i use the SQL SHOW GRANTS, i receive:

"Grants for xxx@10.0.% GRANT USAGE ON . TO 'xxxl'@'10.0.%' IDENTIFIED BY PASSWORD GRANT ALL PRIVILEGES ON 'database_name'.* TO 'xxx'@'10.0.%' WITH GRANT OPTION.

I am using the only MySQL account provided by my host so I assume it is the root user.

$servername = "mysql.domain_name.nl";    
$username = 'xxx';
$password = 'xxx';

$conn = new mysqli($servername, $username, $password);`

$username = $_POST['username'];
$password =  $_POST['password'];
$query = "INSERT INTO 'game'.'login_data' ('username', 'password') VALUES ('".$username."', '".$password."')";
$data = mysql_query ($query)or die(mysql_error());

Please help me to gain rights to be able to INSERT.

You are mixing `MySQLi with Prepared Statements` and `MySQL` - stick to just one! — The Codesee, Apr 27 '16 at 15:55
not just that, but this => http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks and who knows what those POST arrays contain and didn't choose a db. — Funk Forty Niner, Apr 27 '16 at 15:57
Don't use mysql_query. It's deprecated and has been deprecated for a long time. Use mysqli_ or PDO. — Edward, Apr 27 '16 at 15:59

Matt · Answer 1 · 2016-04-27T16:08:19.710

1

First you need to read up on using mysqli. Everything you need is here: http://php.net/manual/en/book.mysqli.php

You will see that you are missing 1 parameter from your $conn Needs to be new mysqli('localhost', 'my_user', 'my_password', 'my_db');

$servername = "mysql.domain_name.nl";
$username = 'xxx';
$password = 'xxx';

$conn = new mysqli($servername, $username, $password, **add database**);

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

For the sake of security you want to use prepared statements. Change it to the following:

if ($stmt = $conn->prepare("INSERT INTO `game`.`login_data` (username, password) VALUES (?, ?)")) {
        $stmt->bind_param('ss', $username , $password );
        // Execute the prepared query.
        if (! $stmt->execute()) {
            $stmt->close();
        }
    }
}

Of course this is not the way you should completely treat a user database. You want to be hashing passwords, storing salts etc. But for this question. The above should be enough.

edited Apr 27 '16 at 16:08

answered Apr 27 '16 at 16:01

Matt

1,749
2
12
26

`INSERT INTO 'game'.'login_data' ('username', 'password')` that's a major fail. Wrong type of identifiers qualifiers. – Funk Forty Niner Apr 27 '16 at 16:01
They are the column names. no? Whats wrong with that? – Matt Apr 27 '16 at 16:03
`INSERT INTO 'game'.'login_data' ('username', 'password')` those are single quotes `'` as opposed to what should have either been no quotes or ticks `\`` - Two different animals here ;-) – Funk Forty Niner Apr 27 '16 at 16:07
Was copied and pasted then tidied up. You jumped the gun a tad ;) – Matt Apr 27 '16 at 16:08
I doubt that I jumped the gun Matt *lol* - Be honest with me; would you have not replaced them had I not signaled it? Plus, had I not... you'd of probably been downvoted by someone else who isn't as forgiving as I am, or patient ;-) – Funk Forty Niner Apr 27 '16 at 16:09
Haha I wasn't sure to remove the ' completely or replace with `. But I was in the process of removing ;). Thank you for not downvoting though. Is everything correct/okay now? – Matt Apr 27 '16 at 16:10
Believe me Matt, I'm not the type who will downvote someone unless that person doesn't listen to when I tell them they made an error (it isn't our job to fix someone else's stuff). Only then will I "might" downvote and tell them again that they've made a syntax error and haven't fixed it and this for both the OP and for future readers. Many do not bother to give someone the benefit of the doubt or courtesy; I do. *Cheers* – Funk Forty Niner Apr 27 '16 at 16:13
1

Need more people like you on stack Fred, refreshing to see :). Haven't been around long but here to stay. Thank you. – Matt Apr 27 '16 at 16:16
1

If I can help another to provide good quality answers, I'll always be here (and a few good other good apples I might add) to signal anything that may be out of whack ;-) all the best Matt, keep up the good work. Pro-tip though: I'd add to your answer that they were using the wrong types of quotes (MySQL calls them Identifiers Qualifiers) and here's the link for it http://dev.mysql.com/doc/en/identifier-qualifiers.html and in doing so, that provides a better answer and information for all. Oh, and you're welcome :-) – Funk Forty Niner Apr 27 '16 at 16:18

score 0 · Answer 2 · answered Apr 27 '16 at 16:06

0

I have tidied up your code, fixed the syntax errors, as well as switched the entire code to PDO.

MySQL is deprecated and MySQLi is open to SQL injection hacks, even with mysqli_real_escape_string!

<?php
$user = 'xxx';
$pass = 'xxx';

$conn = new PDO('mysql:host=localhost;dbname=mysql.domain_name.nl', $user, $pass);

$username = $_POST['username'];
$password =  $_POST['password'];
$stmt = $conn->prepare("INSERT INTO `game`.`login_data` ('username', 'password') VALUES (:username, :password)");
$stmt->bindParam(':username', $username);
$stmt->bindParam(':password', $password);
$stmt->execute();
?>

For more information on PDO, consult the manual here: http://php.net/manual/en/book.pdo.php

answered Apr 27 '16 at 16:06

The Codesee

3,714
5
38
78

I am receiving this error: Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [1045] Access denied for user '$user'@'localhost' (using password: YES)' in /register_script.php:5 Stack trace: #0 /register_script.php(5): PDO->__construct('mysql:host=loca...', '$user', '$pass') #1 {main} thrown in /register_script.php on line 5 – Tom Apr 27 '16 at 17:19
@Tom Do you mind me asking which free hosting provider you are using? I have a feeling that the username isn't `root` and that you have to set one (a database) yourself. – The Codesee Apr 27 '16 at 17:51
i am using DataCT, it is dutch. I have set my own username and password with them for SQL. – Tom Apr 27 '16 at 17:58
@Tom You will have to set the username and password in the `$user` and `$pass` variables, as well as enter the correct database in `$conn` – The Codesee Apr 27 '16 at 18:14
@Tom Can you post your database connection? – The Codesee Apr 27 '16 at 19:05
`$user = 'myusername'; $pass = 'mypassword'; $conn = new PDO('mysql:host=localhost;dbname=mydbname', '$user', '$pass');` – Tom Apr 28 '16 at 06:34
@Tom I recommend you start a new thread with the error you're receiving. – The Codesee Apr 28 '16 at 06:40

Rights INSERT PHPmyadmin MySQL

2 Answers2