data isn't being inserted into database. not sure what i'm doing wrong

Question

$name = $_POST["name"];
$address = $_POST["address"];
$city = $_POST["city"];
$state = $_POST["state"];
$zipcode = $_POST["zipcode"]; 
$country = $_POST["country"];
$month = $_POST["month"];
$day = $_POST["day"]; 

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "INSERT INTO birthdays (name, address, city, state, zipcode, country, month, day)
VALUES ($_POST["name"], $_POST["address"], $_POST["city"], $_POST["state"], $_POST["zipcode"], $_POST["country"], $_POST["month"], $_POST["day"])";

if ($conn->query($sql) === TRUE) {
    echo "You have been added to the birthday list";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();

does the database prefix need to be included in the login/database? i'm confused right now.

sacrmfuw_sjay is with the prefix.. sjay is without it

format your code... You also need to add more information - I have no idea how you connect. If your question is "Do I need to use the prefix" then my question is "why don't you just try it"? — Cyclonecode, Nov 17 '16 at 19:35
Your variables aren't quoted. If you query did work, you'd be wide open for SQL injection. You're using mysqli, so take advantage of prepared statements and [bind_param](http://php.net/manual/en/mysqli-stmt.bind-param.php), and get rid of those pesky quoting issues. — aynber, Nov 17 '16 at 19:37
You should be getting syntax errors. The `"` for your `POST` values are closing and opening your `$sql` encapsulation. Those values also would need to be quoted to insert. Look at the colors and you should see the issue. You wont get to the `mysqli` error because your PHP never parses. Check your error logs. `Parse error: syntax error, unexpected '"'`-https://3v4l.org/OiUpU — chris85, Nov 17 '16 at 19:40
Other dupes, http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks, http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php, http://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display — chris85, Nov 17 '16 at 19:44
OK, let's start slow and work towards a solution. First, you set variables, but you don't use them in your query. You can use this edit to clean things up: $sql = "INSERT INTO birthdays (name, address, city, state, zipcode, country, month, day) VALUES ('$name', '$address', '$city', '$state', '$zipcode', '$country', '$month', '$day')"; — Jacey, Nov 17 '16 at 19:51

spencer7593 · Answer 1 · 2016-11-17T19:57:12.730

If we are going to include literal values into our SQL text, it is imperative that any potentially unsafe values be properly escaped. The mysqli_real_escape_string function is custom written to do that for us.

To create SQL text that includes literal values, we could do something like this:

  $sql = "INSERT INTO birthdays (name, address, city, state, zipcode, country, month, day)"
  . " VALUES"
  . " ('" . $conn->real_escape_string( $_POST["name"]    ) . "'"
  . ", '" . $conn->real_escape_string( $_POST["address"] ) . "'"
  . ", '" . $conn->real_escape_string( $_POST["city"]    ) . "'"
  . ", '" . $conn->real_escape_string( $_POST["state"]   ) . "'"
  . ", '" . $conn->real_escape_string( $_POST["zipcode"] ) . "'"
  . ", '" . $conn->real_escape_string( $_POST["country"] ) . "'"
  . ", '" . $conn->real_escape_string( $_POST["month"]   ) . "'"
  . ", '" . $conn->real_escape_string( $_POST["day"]     ) . "'"
  . ")";

The mysqli_real_escape_string function is used for values being included in the statement. If any of the identifiers (column names, table name) are MySQL Reserved Words, those also need to be properly escaped typically by enclosing the identifier in backtick characters.

The preferred pattern, as an alternative to including literal values in the SQL text, is to use prepared statements with bind placeholders. A static SQL statement like this:

  $sql = "INSERT INTO birthdays (name, address, city, state, zipcode, country, month, day)"
  . " VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

Then prepare the SQL text into a statement, and bind_param the values to the statement, and then execute the statement.

http://php.net/manual/en/mysqli-stmt.bind-param.php

got it solved.. from this `$sql = "INSERT INTO birthdays (name, address, city, state, zipcode, country, month, day) VALUES ($_POST["name"], $_POST["address"], $_POST["city"], $_POST["state"], $_POST["zipcode"], $_POST["country"], $_POST["month"], $_POST["day"])";` to this `$sql = "INSERT INTO sacrmfuw_birthdays.birthdays (name, address, city, state, zipcode, country, month, day) VALUES ('$name', '$address', '$city', '$state', '$zipcode', '$country', '$month', '$day')";` — Jay S, Nov 17 '16 at 20:13
@JayS: That pattern appears to be *vulnerable* to SQL Injection. Consider what will happen when $city is "`O'Fallon`", or $name is "`D'Onofrio`"... or something more nefarious, like [Little Bobby Tables](https://xkcd.com/327/) Don't go down the path of adopting coding patterns that are vulnerable to SQL Injection, when it's *not that hard* to adopt patterns that are *not* vulnerable https://www.owasp.org/index.php/SQL_Injection — spencer7593, Nov 17 '16 at 22:02
@JayS: Spencer is correct. You should never, ever, ever, EVER do it that way. Very bad form. In addition to `bind_param()` (the link in this answer), you should read about `real_escape_string()` http://php.net/manual/en/mysqli.real-escape-string.php — Karl Wilbur, Nov 17 '16 at 22:36

data isn't being inserted into database. not sure what i'm doing wrong

1 Answers1