Grabbing IP on submit, SQL syntax error

Question

After browsing google for a few hours, I managed to splice together some code up, and it looks like it's working for the most part. Unfortunately, I'm getting an SQL error when I submit my form.

What I'm trying to do: When someone fills out the form on my website, a specific function is applied based on which radio button is pressed in the form. I want to store the data in a database, but I also want to store the IP address of the individual submitting.

All I request of this wonderful community is an explanation of why this isn't functioning as I thought it should, and a quick lesson on how to prevent this from happening again.

Here is the code for my form:

<html>
<head>
<link rel="stylesheet" type="text/css" href="/style.css">
    <title> 
    Learning Made Easy
    </title>
</head>
<body>

    <?php include_once 'googleanalytics.php'; ?>

<a href="http://terrythetutor.com">
    <div class="banner"> </div>
</a>

<?php include 'menu.php'; ?>

<div class="content">
</br>
</br>
</br>
    <form action="../scripts/switch.php" method="post">
    Title: 
    </br><input type="text" name="Title">
</br>
</br>
</br>
    Summary of the video (including questions used in the video): 
    </br><textarea name="Summary" COLS=60 ROWS=10></textarea>
</br>
</br>
</br>
    URL of the video (Yes, this means you need to upload it to an external website.): 
    </br><input type="text" name="URL">
    </br>
    </br>
    Which course does your video pertain to?</br>
    <input type="radio" name="course" value="intermediate"> Intermediate and below</br>
    <input type="radio" name="course" value="college"> College Algebra</br>
    <input type="radio" name="course" value="precalculus"> PreCalculus</br>
    <input type="radio" name="course" value="trigonometry"> Trigonometry</br>
    <input type="radio" name="course" value="calculus I"> Calculus I</br>
    <input type="radio" name="course" value="calculus II"> Calculus II</br>
    <input type="radio" name="course" value="calculus III"> Calculus III</br>
    <input type="radio" name="course" value="differential equations"> Differential Equations</br>
    </br>

The function triggered is used to pick the correct function based on the radio button selected. For the sake of space I won't include it, and will skip right to the code that it redirects to. This is where (I suspect) my error is, and I'm unfortunately not well versed enough to solve this error alone.

Code of the function AFTER switch.php (this is where I define the IP variable):

<?php
// Create connection
$con=mysqli_connect("********","*****","*****","****");
$IP = $_Server['REMOTE_ADDR'];
// Check connection
if (mysqli_connect_errno($con))
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

  $sql="INSERT INTO Intermediate Algebra ('Title', 'URL', 'IP', 'Summary')
VALUES 
('$_POST[Title]','$_POST[URL]','[$IP]','$_POST[Summary]'";

if (!mysqli_query($con,$sql))
  {
  die('Error: ' . mysqli_error($con));
  }

echo "Your video has been successfully submitted. Thank you for your contribution to TerryTheTutor.com";

header('Location:http://terrythetutor.com');
  ?>
        </br>
        <input type="submit" value="Submit, foo!">
        </form>
    </br>
    </br>
    </br>
    <p>
    Please understand that you will not be able to change the title, summary, or URL of your video after submission.
    </p>


    </div>

    <div class="footer">
        <?php include 'footer.php'; ?>
    </div>


    </body>

    </html>

I believe that the error has originated with the $IP variable. I've tried to add quotes, scanned the code countless times and still am unsure of what the error is.

Here is what the error I'm getting when I submit looks like:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Algebra ('Title', 'URL', 'IP', 'Summary') VALUES ('Title Test','Url test','[]',' at line 1

As a courtesy, if someone could show me how to properly "sanitize" this data input, that would be wonderful.

Thank you, guys!

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

table names and column names are identifiers. they are not string literals so they should not be wrap with single quote. So you need to remove it eg

INSERT INTO `Intermediate Algebra` (Title, URL, IP, Summary) VALUES(....)

If it happens that the names contains spaces or a reserved keyword, it should be wrap with backticks. eg

INSERT INTO `Intermediate Algebra` (`Title`, `URL`, `IP`, `Summary`) VALUES(...)

Additional Info

MySQL Reserved Keywords List

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

Your comment about the single quotes seemed to be the only thing that was wrong. I can't thank you enough. :) — user2666324, Dec 14 '13 at 13:53

Krish R · Answer 2 · 2013-12-14T05:42:54.190

0

You need to close bracket ),

('$_POST[Title]','$_POST[URL]','$IP','$_POST[Summary]')";

SQL:

    $Title = mysqli_real_escape_string($con, $_POST['Title']);
    $URL = mysqli_real_escape_string($con, $_POST['URL']);
    $IP = $_SERVER['REMOTE_ADDR'];
    $IP = mysqli_real_escape_string($con, $IP);
    $Summary = mysqli_real_escape_string($con, $_POST['Summary']);
    $sql="INSERT INTO Intermediate Algebra ('Title', 'URL', 'IP', 'Summary')
       VALUES
       ('$Title','$URL','$IP','$Summary')";

edited Dec 14 '13 at 05:42

answered Dec 14 '13 at 04:36

Krish R

22,583
7
50
59

I hadn't thought of escaping like this before. Much appreciated, user876345 ;). – user2666324 Dec 14 '13 at 14:00

score 0 · Answer 3 · answered Dec 14 '13 at 04:47

0

You can try this, this will help you to run your script successfully without any error.

$sql="INSERT INTO `Intermediate Algebra` (`Title`, `URL`, `IP`, `Summary`) 
VALUES ('".$_POST[Title]."','".$_POST[URL]."','".$IP."','".$_POST[Summary]."')";

answered Dec 14 '13 at 04:47

Bhupendra

248
2
7

score 0 · Answer 4 · answered Dec 14 '13 at 04:50

You have more than one error here:

PHP variable names are case sensitive. This means you have an error in this line of code:

$IP = $_Server['REMOTE_ADDR'];

Thus, $IP is always empty.

Instead, this should be:

$IP = $_SERVER['REMOTE_ADDR'];

In your SQL statement, table names with spaces need to be backquoted. It's also a very good idea to do the same with your field names, to avoid conflicts with MySQL keywords:

$sql="INSERT INTO `Intermediate Algebra` (`Title`, `URL`, `IP`, `Summary`) VALUES(...)";

Finally, your SQL statement is vulnerable to SQL injection and needs to be completely rewritten and replaced with a prepared statement.

Ah man, you guys are great. LOL. Thank you very much for clearing that up. I was stuck at that forever. — user2666324, Dec 14 '13 at 13:56

Grabbing IP on submit, SQL syntax error

4 Answers4