problem saving to mysql database php mysqli

Question

Hi all i'm trying to save data to database and i get an error i never saw before i have a hunch it has something to do with the db collation but I'm not sure whats wrong,

here is the query:

$query1 = "INSERT INTO scape.url (url,normalizedurl,service,idinservice) VALUES (url, normalizedurl, 4, 45454)";
            $query = "INSERT INTO scape.url (url, normalizedurl, service, idinservice) VALUES ("
            .$sql->real_escape_string($this->url).","
            .$sql->real_escape_string($this->normalizedUrl).","
            .$sql->real_escape_string($this->service).","
            .$sql->real_escape_string($this->idInService).")";
            $result = $sql->query($query);
            echo $sql->error;

the error massage i get is :

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 '://www.something/here/here/here/12345,httpwwwsomthighere' at line 1

database collation for this fields is utf8-general-ci and field type is varchar 255

any ideas on that?

Answer 1

If you do it this way, you still have to put quotes around the strings (url and normalizedurl). That's the syntax problem its referring to.

That being sad, that's not the recommended way of passing parameters with mysqli. The whole point of mysqli is that it has query parameterization. For example:

$mysqli = new mysqli($host, $user, $password, $database);
if (mysqli_connect_errno()) {
  printf("Connect failed: %s\n", mysqli_connect_error());
  exit;
}
$sql = <<<END
INSERT INTO scape.url (url,normalizedurl,service,idinservice)
VALUES (?, ?, ?, ?)
END;
$stmt = $mysqli->prepare($sql);
if ($stmt === false) {
  printf("Error executing %s: %s\n", $sql, $stmt->error);
  exit;
}
$stmt->bind_param('ssii', $this->url, $this->normalizedUrl,
  $this->service, $this->idInService);
$stmt->execute();