The inclusion of " ' " is breaking MySQL inserts

[WorldNews]

Hello,

Background:
We offer a free Blog service.
so of course what happens is that after someone writes an article, a story, for their Blog it is inserted into a MySQL table.

I just realized that the inclusion of " ' " in a sentence is breaking MySQL insert commands. For example if a sentence is like: "This won't make it right............", that will break the MySQL Insert
Whereas "This wont make it right............", is Ok.

Producing this Error message like:

There was an Error with your Article Submission: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 't into this ', '

How do I address this issue/problem?

Regards,

[crmalibu]

mysql_real_escape_string() must be used on all values to be placed into a query, unless its a numeric value.

[Phidev]

My recommendation would be to use regular expressions and preg_replace() to cover all 'non-usual' characters such as &, ' , ñ, á and so on, to be replaced with their html entity encoding then your ' should be replaced by ´ and & by & this way your code will be valid XHTML. Same for " = "

Because sooner or later someone will input those values.

Also I would do what crmalibu suggests just for database sanitizing.

Good Luck

[Jake Arkinstall]

Phidev, you can just use HTMLEntities() for that - RegEx would be a bit over the top for this situation (It's not the most efficient of things).

But that's for output. When it's being input to the database, mysql_escape_real_string() is fine.

Even better, you could use prepared statements in PDO - then there is no way to inject code at all.

[Young Twig]

mysql_real_escape_string() must be used on all values to be placed into a query, unless its a numeric value.

If it's a numeric value, make sure you do some type of typecasting or something. Typically intval or floatval will do what you want for numbers.

[WorldNews]

Greetings,

Thanks for your tip.
But how do I actually use this?

I mean lets say that the body of the article is:

$body = $_POST['body']

and it is inserted into the MySQL table with an insert command:

INSERT INTO xyz (body, user, ip) VALUES ('$body', '$user_id', '$ip')

So how do I use the command that you have suggested to remove the unwanted characters that are going to break MySQL before inserting
the $body into this MySQL table?

Regards,

mysql_real_escape_string() must be used on all values to be placed into a query, unless its a numeric value.

[sk89q]

"INSERT INTO xyz (body, user, ip) VALUES ('".mysql_real_escape_string($body)."', '".mysql_real_escape_string($user_id)."', '".mysql_real_escape_string($ip)."')" 

Although I recommend using PDO and prepared statements/binding your parameters, because it lowers the chance of you accidentally introducing an exploit (it happens...), and for the sheer fact that reading it in your code is friendlier on the eyes.

If you are unable or unwilling, you can use my http://php.net/manual/en/function.mysql-query.php#81188 as a compromise.

[crmalibu]

So how do I use the command that you have suggested to remove the unwanted characters that are going to break MySQL before inserting
the $body into this MySQL table?

The php manual offers documentation, and often examples on how to use functions. There's usually also tons of user contributed notes which offer additional information.

[WorldNews]

Hi,

So mysql_real_escape_string is a PHP function.
I was not clear about this from your 1st message.
Now that I know it is a function then obviously that answers
the question. And FYI: it works fine of course.

Now about the rest of your message: why are you suggesting
that I use your php code listed via the URL you have provided?
What sort of (real) danger, by example please, can mysql_real_escape_string
lead to?

Regards,

"INSERT INTO xyz (body, user, ip) VALUES ('".mysql_real_escape_string($body)."', '".mysql_real_escape_string($user_id)."', '".mysql_real_escape_string($ip)."')" 

Although I recommend using PDO and prepared statements/binding your parameters, because it lowers the chance of you accidentally introducing an exploit (it happens...), and for the sheer fact that reading it in your code is friendlier on the eyes.

If you are unable or unwilling, you can use my http://php.net/manual/en/function.mysql-query.php#81188 as a compromise.

[WorldNews]

Real funny
Of course I know how to use a function.
I was just not clear that mysql_real_escape_string was a function.
In hindsight I should have been sure that it was a function.
I just needed to make sure.

Thanks anyway.

The php manual offers documentation, and often examples on how to use functions. There's usually also tons of user contributed notes which offer additional information.