PHP insert statement not working :(

Question

The error message I get is:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''Player Name', 'Player Age', 'Player Team') VALUES ('Willian', '27', 'Chelsea')' at line 1 in C:\xampp\htdocs\WebProgrammingWebsite\add-player.php:9 Stack trace: #0 C:\xampp\htdocs\WebProgrammingWebsite\add-player.php(9): PDO->prepare('INSERT INTO pla...') #1 {main} thrown in C:\xampp\htdocs\WebProgrammingWebsite\add-player.php on line 9

I have double checked and tried to change what is written on line 9 like it says but I just can't get it to work. I have also tried to use the whole "Bind_param" thing but I'm just a noob and nothing is working. It has to be some dumb syntax mistake on my part but it's driving me crazy Here is my code:

<?php
  require("connect.php");

  if(isset($_POST['submit'])) {
    $name = $_POST['p_name'];
    $age = $_POST['p_age'];
    $team = $_POST['p_team'];

    $stmt = $con->prepare("INSERT INTO player ('Player Name', 'Player Age', 'Player Team') VALUES ('".$name."', '".$age."', '".$team."')");
    $stmt->execute();
    $stmt->closeCursor();

    header("Location: display-players.php");
  }
?>
<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <title>Football Database</title>
    <link rel="stylesheet" type="text/css" href="css/style.css">
    <link href="https://fonts.googleapis.com/css?family=Roboto:300,400,500,700" rel="stylesheet">
  </head>
  <body>
        <div class="wrapper">
        <form class="add" action="" method="post">
          <label>Player Name</label>
          <input type="text" name="p_name"><br>

          <label>Player Age</label>
          <input type="text" name="p_age"><br>

          <label>Player Team</label>
          <input type="text" name="p_team"><br>

          <input id="submit" name="submit" type="submit" value="Submit">
        </form>
      </div>
      <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
      <script type="text/javascript" src="resources/js/main.js"></script>
  </body>
</html>

Your script is at risk of [SQL Injection Attack](//stackoverflow.com/questions/60174) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](//stackoverflow.com/questions/5741187) Use [prepared parameterized statements](https://php.net/manual/en/mysqli.quickstart.prepared-statements.php). — John Conde, Jan 17 '18 at 19:11
Remember that error message. It will serve you well next time. — Funk Forty Niner, Jan 17 '18 at 19:12
**WARNING**: When using PDO you should be using [prepared statements](http://php.net/manual/en/pdo.prepared-statements.php) with placeholder values and supply any user data as separate arguments. In this code you have potentially severe [SQL injection bugs](http://bobby-tables.com/). Never use string interpolation or concatenation and instead use [prepared statements](http://php.net/manual/en/pdo.prepared-statements.php) and never put `$_POST`, `$_GET` or any user data directly in your query. Refer to [PHP The Right Way](http://www.phptherightway.com/) for general guidance and advice. — tadman, Jan 17 '18 at 19:14
Thanks everyone got it sorted, sorry for the duplicate. I thought I am using prepared statements to prevent Injection Attack? — Cian, Jan 17 '18 at 19:15
Your columns have spaces in them which is, for most seasoned developers, *super annoying* because they require special escaping when used. You should steer towards column names that are short, concise, and self-explanatory. `playerName` or `player_name` is vastly better than `Player Name`. — tadman, Jan 17 '18 at 19:15
You've got half of it right, you're using prepared statements, but you're not using placeholder values, and that's where the magic happens. You should avoid putting `$x` type values directly in your query, or using concatenation, that's text-book injection. Instead use a placeholder and on `execute()` call it. — tadman, Jan 17 '18 at 19:16
Step 1: `$stmt = $con->prepare("INSERT INTO player (\`Player Name\`, \`Player Age\`, \`Player Team\`) VALUES (:name., :age, :team.)");`. Step 2: `$stmt->execute(array('name' => $_POST['p_name'], ...))`. Note the backticks on the column names, as single quotes designate string data, not columns. — tadman, Jan 17 '18 at 19:17
It's not that hard to do in practice, and usually makes your queries a lot less messy, and so, easier to understand and find bugs. — tadman, Jan 17 '18 at 19:18
I attempted to try it out given the example you gave. I wrote — Cian, Jan 17 '18 at 19:21
$stmt = $con->prepare("INSERT INTO player (`Player Name`, `Player Age`, `Player Team`) VALUES (:name., :age, :team.)"); $stmt->execute(array('name' => $name, 'age' => $age, 'team' => $team)); — Cian, Jan 17 '18 at 19:22
and i got an error, I'm guessing I made a mistake somewhere? — Cian, Jan 17 '18 at 19:22
Since this question has been closed, open a new one with the new code you're using and the exact error you're getting. I'm sure you're closer to solving this now. — tadman, Jan 17 '18 at 19:30
@tadman I'll have to wait another 90 mins to ask another question haha — Cian, Jan 17 '18 at 19:36
@tadman nevermind I fixed it. I got rid of the two periods that were infront of and behind the placeholders in the insert statement — Cian, Jan 17 '18 at 19:37
@Cian Gives you some time to hack around with it for a bit. Have a look at guides [like this](https://phpdelusions.net/pdo) or [the manual](http://php.net/manual/en/book.pdo.php) if you're really stuck. — tadman, Jan 17 '18 at 19:37

PHP insert statement not working :(

0 Answers0