SQL Syntax Error Integers

Question

I`m trying to have a form that writes to a mysql database using php and html. After submitting the form I get the error

MySQL 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 '', '1', '1362154007', '127.0.0.1'' at line 2

The code to the submission php file is

<?php
require 'connection.php';
$ip = $_SERVER['REMOTE_ADDR'];
$sql="INSERT INTO entries (summoner, role, level, time, ip)
VALUES ('" . mysql_real_escape_string($_POST['summoner']) . "', " . mysql_real_escape_string($_POST['role']) . "', '" . intval($_POST['level']) . "', '" . time() . "', '" . $ip . "'"; 
if (!mysql_query($sql)) die("MySQL error: " . mysql_error());
echo "1 record added";
?>

and the code to line two is

<?php
$con = mysql_connect("localhost", "ratchet132", "password", "lookingforq") or die(mysql_error());
mysql_select_db("lookingforq", $con) or die(mysql_error());
header("Content-Type: text/html; charset=utf-8");
mysql_set_charset("utf8");
mb_internal_encoding("UTF-8");
?>

The error only occurs with integers that are not submitted by the html form (although the level is submitted by it, but it seems to due to the same reason as the others, not the forms). I'm thinking this is probably an error with how I have my MYSQL table set up but I can't figure out what I've done wrong. Any help would be awesome.

[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](http://j.mp/XqV7Lp). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. — Kermit, Mar 01 '13 at 16:21

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

1

there is an extra single quote in your integer value,

VALUES ('" . mysql_real_escape_string($_POST['summoner']) . "', " . mysql_real_escape_string($_POST['role']) . "', '" . intval($_POST['level']) . "', '" . time() . "', '" . $ip . "'"; 
                                                                                                                ^ HERE

My suggestion is to store the values in variable first so it is easy to debug the code, eg

$summoner = mysql_real_escape_string($_POST['summoner']);
$role = mysql_real_escape_string($_POST['role']);
$intV = intval($_POST['level']);
$sTime = time();
$ip = $_SERVER['REMOTE_ADDR'];
$sql="INSERT INTO entries (summoner, role, level, time, ip) 
      VALUES ('$summoner', $role, $intV, $sTime,'$ip' )')";

Use PDO or MySQLi extension so you can paramaterized the query. The link below talks about SQL Injection but it also shows there the usage of PDO and MySQLi Extension.

How to prevent SQL injection in PHP?

edited May 23 '17 at 12:21

Community

1
1

answered Mar 01 '13 at 16:15

John Woo

258,903
69
498
492

I get this now instead: `MySQL 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 '', 1', '1362154579', '127.0.0.1'' at line 2` – ratchet132 Mar 01 '13 at 16:17
1

The ***only*** solution here is to use binded parameters and prepared statements. This is the closest answer to that solution. – Kermit Mar 01 '13 at 16:22
@AarolamaBluenk yep, I added a link on my answer which shows how prepared statements are used. `:D` – John Woo Mar 01 '13 at 16:23
I'm getting this at the moment. `MySQL 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 '')' at line 2` If I remove those characters it results in a `MySQL error: Unknown column 'two' in 'field list'` – ratchet132 Mar 01 '13 at 16:30
oh what query are you execute? there is no `two` on the column you have shown. – John Woo Mar 01 '13 at 16:33
Would you like to look at the form code or what I currently have for the actual php action? – ratchet132 Mar 01 '13 at 16:39
if you may.. hey, who don't you want to use `PDO` or `MySQLi`? – John Woo Mar 01 '13 at 16:40
It seems even more confusing haha :P – ratchet132 Mar 01 '13 at 16:41
ok, can youpost the whole query you have executed? – John Woo Mar 01 '13 at 16:43
Submit.php's code: `` – ratchet132 Mar 01 '13 at 16:50
HTML `

` – ratchet132 Mar 01 '13 at 16:50
which of this are integer? summoner, role, level, time, ip – John Woo Mar 01 '13 at 16:51
Level and time, ip and the rest are varchar. – ratchet132 Mar 01 '13 at 16:54
and which means summoner, role are integers. here, try this, `INSERT INTO entries (summoner, role, level, time, ip) VALUES ($summoner, $role, '$intV', '$sTime','$ip')` – John Woo Mar 01 '13 at 16:55
IT WORKS! :D THANK YOU SO MUCH edit: When I type in the first textbox text instead of a number (first textbox is for the varchar) it results in the error `MySQL error: Unknown column 'my' in 'field list'` – ratchet132 Mar 01 '13 at 17:01
so the problem has been solved? `:D` – John Woo Mar 01 '13 at 17:09
Half-solved haha. It works but only when I enter numbers within the html forms (summoner and role). `MySQL error: Unknown column 'my' in 'field list'` is what I get if I enter "my" – ratchet132 Mar 01 '13 at 17:11
as you can see, the value is not wrap with single quote since it is an integer. i guess you need to trap the user inputs before inserting on the database. – John Woo Mar 01 '13 at 17:13
The fields I'm having problems with are the var char ones, not the integer ones anymore, but if I insert integers into the var char forms it works. – ratchet132 Mar 01 '13 at 17:16

score 0 · Answer 2 · answered Mar 01 '13 at 16:15

0

Why you wrap int value in quotes?

'" . intval($_POST['level']) . "'

answered Mar 01 '13 at 16:15

Hamlet Hakobyan

32,965
6
52
68

SeanWM · Answer 3 · 2013-03-01T16:30:49.870

0

You're missing a '. Try:

$sql="INSERT INTO entries (summoner, role, level, time, ip)
VALUES ('" . mysql_real_escape_string($_POST['summoner']) . "', '" . mysql_real_escape_string($_POST['role']) . "', '" . intval($_POST['level']) . "', " . time() . ", '" . $ip . "'";

edited Mar 01 '13 at 16:30

answered Mar 01 '13 at 16:15

SeanWM

16,789
7
51
83

This got me somewhere! :D It solved the problem but now it says `MySQL 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 '' at line 2` I'm thinking there's an extra comma or something that's causing it? I can't find it though – ratchet132 Mar 01 '13 at 16:21
removed the `'` from `time()`. Try now. It's hard to tell without seeing your database setup. – SeanWM Mar 01 '13 at 16:31

SQL Syntax Error Integers

3 Answers3