Error in SQL syntax (quotes ???!!!)

Question

Can someone help me to debug this

<?php
$file_name = basename(__FILE__,'.php');
include("conf.php");
include("XMLSoccer.php");

$years = 1;    ///<-------NUMBER OF YEARS TO GO BACK
$leagueretrive = 3;   ///<--------THE LEAGUE ID TO RETRIEVE DATA FOR

$date1 = date('y', strtotime("-$years years"));
$date2 = date("y");
//CHECKING IF TABLE EXIST IF NOT CREATE NEW
$table = $file_name;
$query = "SELECT ID FROM " . $table;
$resultat = mysqli_query($conn,$query);


if(empty($resultat)) {
    echo "<p>" . $table . " table does not exist</p>";
    $query = mysqli_query($conn,"CREATE TABLE IF NOT EXISTS $file_name (
        Id int NOT NULL PRIMARY KEY,
        HomeGoalDetails varchar(800) NOT NULL,
    )CHARACTER SET utf8 COLLATE utf8_general_ci");
}
else {
    echo "<p>" . $table . "table exists</p>";
} // else

/////GETING THE DATA FROM SERVICE

try {
    $soccer = new XMLSoccer($api_key);
    $soccer->setServiceUrl("http://www.xmlsoccer.com/FootballDataDemo.asmx");
    $results = $soccer->GetHistoricMatchesByLeagueAndSeason(array("league"=>$leagueretrive,"seasonDateString"=>"$date1$date2"));
    print_r($results);
} catch (XMLSoccerException $e) {
    echo "XMLSoccerException: " . $e->getMessage();
}
foreach ($results->Match as $team) {
    $id = $team->Id;
    $homeGoalDetails = $team->HomeGoalDetails;

///INSERTING DATA INTO THE TABLE
    $sql = "INSERT INTO $file_name (HomeGoalDetails)
VALUES ('$homeGoalDetails')
on duplicate key update HomeGoalDetails='$homeGoalDetails'";
}
    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
$conn->close();
?>

The response what i get

Error: INSERT INTO testing2 (HomeGoalDetails) VALUES ('35': Stefan Johansen;4': penalty Leigh Griffiths;') on duplicate key update HomeGoalDetails='35': Stefan Johansen;4': penalty Leigh Griffiths;' 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 ': Stefan Johansen;4': penalty Leigh Griffiths;') on duplicate key update HomeGo' at line 2 Process finished with exit code 0

where is the `INSERT INTO testing2.....` query in your code ? — Happy Coding, Jul 26 '16 at 11:36
Your error is with this line: $sql = "INSERT INTO $file_name (HomeGoalDetails) VALUES ('$homeGoalDetails') on duplicate key update HomeGoalDetails='$homeGoalDetails'"; — Marc Giroux, Jul 26 '16 at 11:39
This is probably something that prepared statements would have fixed for you had you used them — apokryfos, Jul 26 '16 at 11:42
Also you defined a primary key but don't seem to be inserting a primary key, are you sure you didn't mean to also make it `AUTO_INCREMENT` ? — apokryfos, Jul 26 '16 at 11:44
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) — Jay Blanchard, Jul 26 '16 at 12:26
I tryed with prepared statements `$sql = $conn->prepare( "INSERT INTO $file_name (HomeGoalDetails) VALUES ('$homeGoalDetails') on duplicate key update HomeGoalDetails='$homeGoalDetails'"); $sql->bind_param('$homeGoalDetails');` but it doesent work. Also i have deleted the primary key — vivi, Jul 26 '16 at 12:30

Alex H · Answer 1 · 2016-07-26T12:37:26.843

You've got unescaped quotes in your query.

Try:

<?php
$file_name = basename(__FILE__,'.php');
include("conf.php");
include("XMLSoccer.php");

$years = 1;    ///<-------NUMBER OF YEARS TO GO BACK
$leagueretrive = 3;   ///<--------THE LEAGUE ID TO RETRIEVE DATA FOR

$date1 = date('y', strtotime("-$years years"));
$date2 = date("y");
//CHECKING IF TABLE EXIST IF NOT CREATE NEW
$table = $file_name;
$query = "SELECT ID FROM " . $table;
$resultat = mysqli_query($conn,$query);


if(empty($resultat)) {
    echo "<p>" . $table . " table does not exist</p>";
    $query = mysqli_query($conn,"CREATE TABLE IF NOT EXISTS $file_name (
        Id int NOT NULL PRIMARY KEY,
        HomeGoalDetails varchar(800) NOT NULL,
    )CHARACTER SET utf8 COLLATE utf8_general_ci");
}
else {
    echo "<p>" . $table . "table exists</p>";
} // else

/////GETING THE DATA FROM SERVICE

try {
    $soccer = new XMLSoccer($api_key);
    $soccer->setServiceUrl("http://www.xmlsoccer.com/FootballDataDemo.asmx");
    $results = $soccer->GetHistoricMatchesByLeagueAndSeason(array("league"=>$leagueretrive,"seasonDateString"=>"$date1$date2"));
    print_r($results);
} catch (XMLSoccerException $e) {
    echo "XMLSoccerException: " . $e->getMessage();
}
foreach ($results->Match as $team) {
    $id = $team->Id;
    $homeGoalDetails = $team->HomeGoalDetails;

///INSERTING DATA INTO THE TABLE
    $sql = "INSERT INTO $file_name (HomeGoalDetails)
VALUES ('".mysqli_real_escape_string($conn,$homeGoalDetails)."')
on duplicate key update HomeGoalDetails='".mysqli_real_escape_string($conn,$homeGoalDetails)."'";
}
    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
$conn->close();
?>

PHP Warning: mysqli_real_escape_string() expects exactly 2 parameters, 1 given in C:\111.php on line 44 PHP Warning: mysqli_real_escape_string() expects exactly 2 parameters, 1 given in C:\\111.php on line 45 — vivi, Jul 26 '16 at 12:38
Error: INSERT INTO 111 (HomeGoalDetails) VALUES ('') on duplicate key update HomeGoalDetails=''
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 '111 (HomeGoalDetails) VALUES ('') on duplicate key update HomeGoalDetails=''' at line 1-API-PHP-master (1)\XMLSoccer-API-PHP-master\111.php on line 45 — vivi, Jul 26 '16 at 12:39
I fixed the missing connection parameter in `mysqli_real_escape_string`. Does that fix the problem? — Alex H, Jul 26 '16 at 12:39
No i get thi now Error: INSERT INTO 111 (HomeGoalDetails) VALUES ('35\': Stefan Johansen;4\': penalty Leigh Griffiths;') on duplicate key update HomeGoalDetails='35\': Stefan Johansen;4\': penalty Leigh Griffiths;'
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 '111 (HomeGoalDetails) VALUES ('35\': Stefan Johansen;4\': penalty Leigh Griffit' at line 1 Process finished with exit code 0 — vivi, Jul 26 '16 at 12:40

Error in SQL syntax (quotes ???!!!)

1 Answers1