-4

I am trying to insert values into MySQL database but have not been successful. I have tried to edit the SQL statement several times without success. When I ran the same insert statement inside phpMyAdmin, it worked. I really don't know what is wrong with my code. The most frustrating part is there is no error. mysqli_error($conn) returns nothing.

The following code is what I have been working on:

<!DOCTYPE html>
<html>
<head>
</head>
<body>
<?php

$servername = "XXXXXXXXXXXXXXX";
$database = "RRRRRRRRRRRRR";
$username = "DDDDDDDD";
$password = "ZZZZZZ";



$data = json_decode(file_get_contents('php://input'), true);
$uid = $data["id"];
$firstname = $data["firstname"];
$lastname = $data["lastname"];
$email = $data["email"];
$password = $data["password"];
$activationCode = $data["activationCode"];

     

$conn = mysqli_connect($servername, $username, $password, $database);
// Check connection    

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}else{
    echo "Connected successfully";
}

$sql = "INSERT INTO studentinfo (studentid, firstname, lastname, email, mypass, activationCode)
VALUES ('$uid', '$firstname', '$lastname', '$email', '$password', '$activationCode')";


if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    print mysqli_error($conn);
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);
?>
</body>
</html>

The following is what was return from echo $SQL:

INSERT INTO studentinfo (studentid, firstname, lastname, email, mypass, activationCode) VALUES ('androidid', 'wendu', 'Chao', 'eeeee@yahoo.com', 'xxxxxxxx', '0ecccvvjdkfkf')
defemz
  • 519
  • 2
  • 7
  • 20
  • 2
    Your script is open to [SQL Injection Attack](http://stackoverflow.com/questions/60174). Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187) You should alway use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's instead of concatenating user provided values into the query. Never trust ANY user input! – RiggsFolly Jan 20 '21 at 11:00
  • 2
    You are storing Plain Text Passwords **V.Bad** PHP provides [`password_hash()`](http://php.net/manual/en/function.password-hash.php) and [`password_verify()`](http://php.net/manual/en/function.password-verify.php) please use them for the safety of your users. – RiggsFolly Jan 20 '21 at 11:01
  • 1
    Show `$sql` variable value. – Akina Jan 20 '21 at 11:02
  • @RiggsFolly Thanks for the reply. Yes i am currently trying to get this to work first(still testing), will attend to your suggestion, when it works. thanks. – defemz Jan 20 '21 at 11:03
  • Does one of your variable (lastname for example) look like `O'Toole` by any chance – RiggsFolly Jan 20 '21 at 11:05
  • kindly print your $sql variable before if else to see what exactly is your madeup query – Hammad Ahmed khan Jan 20 '21 at 11:07
  • Where are the variables ? – Browyn Louis Jan 20 '21 at 11:17
  • 1
    It's very clear that `$uid`, `$firstname` ...etc are never defined anywhere in your script. So clearly they won't have any value, which will then screw up your query (even more so because you're not creating the queries properly using parameters). It's not clear whether "without success" means it's not inserting anything at all, or whether it's inserting a row containing blank values. It's far better to be explicit about exactly what your code is / isn't doing, rather than alluding vaguely to a problem. – ADyson Jan 20 '21 at 11:31
  • `When i ran the same insert statement inside MyphpAdmin, it worked`...I doubt you ran _exactly_ the same statement, because it probably had hard-coded values rather than PHP variables in it. That alone should have been a clue about where to start looking for your problem. – ADyson Jan 20 '21 at 11:33
  • It's also possible that your connection threw an error, but you might have ignored the warning. Enable proper mysqli error reporting. – Dharman Jan 20 '21 at 11:33
  • 1
    Also...why are you inserting a value for `studentid`? This should be an auto_increment field. Don't create your key values by hand...it's too easy to end up with duplicates by accident. – ADyson Jan 20 '21 at 11:34
  • 1
    Thanks for the edit. It's unclear how those values came to exist, because as I said above, your script never defines any of the variables. Therefore they cannot possibly have any values in them. Are you sure you're showing us the correct code? The code you've shown wouldn't generate the SQL you've shown. Have you missed something out, perhaps? – ADyson Jan 20 '21 at 11:37
  • Can you run "desc studentinfo" in the PHPMyAdmin SQL console and add the result? That way we get a view over the table structure – Holger Jan 20 '21 at 11:41
  • 1
    Before we proceed further with this question, please rewrite the code to use parameterized statements and enable mysqli error reporting. See [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) – Dharman Jan 20 '21 at 11:42
  • 1
    P.S. `Yes i am currently trying to get this to work first(still testing), will attend to your suggestion, when it works` ...this is not a sensible approach. You're just creating double the effort for yourself by doing that. Because when you make it "work" the insecure way, you'll then have to write a lot of the code again, and then test all of it again, to make sure it still does what you expect. Do it properly the first time and then a) you'll save time, and b) you might find that some of your problems disappear anyway, if they happen to be the result of your poor code. – ADyson Jan 20 '21 at 11:44
  • 1
    ...also, I don't know about your definitions of what counts as "working", but in my team we wouldn't define a piece of code as "working" if it had clear, obvious and well-known security flaws in it. One of the requirements is always to implement basic security procedures. That should be a baseline requirement for all your code and an intrinsic part of your design and coding process, rather than an afterthought. Preventing SQL injection when writing PHP code is a well-documented process, there's really no reason not to do it from the beginning. – ADyson Jan 20 '21 at 11:45
  • Ok thanks for the update. Now it makes a bit more sense how the variables got there. If you need some more guidance on how to set up your system to report errors properly, see these two guides - first you have to configure PHP in general to log errors, then you have to tell mysqli to use PHP's error system to report when problems occur within it. https://stackify.com/php-error-logs-guide/ (php error logging/reporting) https://stackoverflow.com/a/14578644/5947043 (mysqli error handling) – ADyson Jan 20 '21 at 11:58
  • 2
    What makes wonder, why don't you explain **what actually do you see?** I mean, "have not been successful" explains **absolutely nothing** about the actual outcome you get. Does it say created successfully? Or does it say Error? Or may be it doesn't show anything at all? You must always keep in mind that we don't sit behind your screen and your answer relies on your ability to describe the problem – Your Common Sense Jan 20 '21 at 12:28

1 Answers1

0

There are some things I noticed.

The values '$uid', '$firstname', '$lastname', '$email', '$password', '$activationCode' are not set in the code. I assume they are coming from outside over i.e. $_POST

You will also get problems with Strings that contain special characters caused by the injection possibilites. Every ' will break the sql statement and will cause problems. Better use bind parameters instead of passing the values into the statement directly.

$sql = $conn->prepare("INSERT ...");
$sql->bind_param("is", $uid,$firstname);
$sql->execute();

The password ist not hashed. Its best to not safe plain passwords in the database.

I think if you display the value of sql echo $sql; it will tell you how the statement is structured. You can copy&paste that into PHPMyAdmin and see if that statement works like you expected it. $UID seems to be an Integer value but you pass it in as a string.

$sql = "INSERT INTO studentinfo (studentid, firstname, lastname, email, mypass, activationCode)
VALUES ($uid, '$firstname', '$lastname', '$email', '$password', '$activationCode')";
Holger
  • 322
  • 1
  • 4
  • 12