/    Sign up×
Community /Pin to ProfileBookmark

HTML form error SQL Syntax ‘

Copy linkTweet thisAlerts:
Mar 09.2007

I’ve created a blog feature in MySQL/PHP and I’m trying to figure out why I get the following error message:

[CODE]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 ‘2007-03-09 15:03:02’, ‘Mark’)’ at line 5[/CODE]

Here’s my PHP code which is doing the SQL inserting statement.

[code=php]if (isset($_POST[‘submit’])) {
$blog_title = $_POST[‘blog_title’];
$blog_body = $_POST[‘blog_body’];
$blog_user = $_POST[‘blog_user’];
$today = date(“Y-m-d H:i:s”);

$sql_insert = “INSERT INTO blogs
(blog_title, blog_body, blog_date, blog_user)
VALUES
(‘$blog_title’, ‘$blog_body’, ‘$today’, ‘$blog_user’)”;

mysql_query($sql_insert)
or die(mysql_error());

header(“location: ../blog.php”);
}[/code]

The blog_body field is ‘text’ and the text it didnt like was using apostrophes ‘.

Any ideas or pointers would be great.

Thanks in advance.

to post a comment
PHP

3 Comments(s) ↴

Copy linkTweet thisAlerts:
@avaMar 09.2007 — I am familiar with the problem. The problem is that when $blog_body contains apostrophes, it will screw up the query. There are two solutions. First, you can change the quotations into a " mark.

[code=php] $sql_insert = "INSERT INTO blogs
(blog_title, blog_body, blog_date, blog_user)
VALUES
("$blog_title", "$blog_body", "$today", "$blog_user")"; [/code]

But if the variables contain "-marks, you'd have the same problem. The best solution would be to use the addslashes and stripslashes functions to transform the data, but that can prove to be a lot of work if you already have a large part of the site running.
Copy linkTweet thisAlerts:
@pcthugMar 09.2007 — Read up on [url=http://en.wikipedia.org/wiki/SQL_injection]SQL Injection[/url].
[code=php]
if (isset($_POST['submit'])) {
$blog_title = $_POST['blog_title'];
$blog_body = $_POST['blog_body'];
$blog_user = $_POST['blog_user'];
// $today = date("Y-m-d H:i:s"); your just emulating the faster, MySQL NOW() function

$sql_insert = sprintf("INSERT INTO blogs
(blog_title, blog_body, blog_date, blog_user)
VALUES
('%s', '%s', NOW(), '%s')", mysql_real_escape_string($blog_title), mysql_real_escape_string($blog_body), mysql_real_escape_string($blog_user));

mysql_query($sql_insert)
or die(mysql_error());

header("location: ../blog.php");[/code]
Copy linkTweet thisAlerts:
@mfaulknerauthorMar 09.2007 — http://uk2.php.net/manual/en/function.htmlentities.php


This sorted it out. Thanks for your suggestions anyway - especially the MySQL NOW() function.
×

Success!

Help @mfaulkner spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.17,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...