Inserting ' in to a database

[Kemik]

Hello,

 

I've been having problems with a form I'm trying to insert in to a database. It borks if I try and insert any query with a ' in.

 

Basically, I have a form with two fields, one title and one content. They are passed to the database like so:

 

<?php  $q = "INSERT INTO ".TBL_NEWS." (title, content, username, timestamp) VALUES ('$title', '$content', 'Kemik', '$time')"; ?>

 

I do use stripslashes() on the variables and then check them via eregi however I'm not sure of any other way to ensure I can submit a sentence such as Why won't this thing work? without breaking the query and ensuring I can display it from the database on to a different php page the same way as I inputted it.

 

Any help would be appreciated.

 

 

 

[drewbee]

Every single variable that goes into the database should be protected with mysql_real_escape_string()

 

It will also take care of your single quote issue.

 

so

 

<?php  

$q = "INSERT INTO ".TBL_NEWS." (title, content, username, timestamp) VALUES ('".mysql_real_escape_string($title)."', '".mysql_real_escape_string($content)."', 'Kemik', '$time')"; 

?>

[Kemik]

So wrap all variables being passed in a query in the mysql_real_escape_string()? How come out of most of the tutorials/scripts I've seen most people are missing this step? Are there other functions that do the same job but called something else or is it just a common mistake?

[lightningstrike]

You should definitely use mysql_real_escape_string() or you risk a threat to possible damage and theft of data in your databases tables.

 

Alternative functions exist but can be less reliable.

[chronister]

addslashes() will do pretty much the same thing.

 

Most people miss this step because there are too many insecure programming tutorials out there. The exact problem your running into can be used to do mysql injection attacks. Just make it a habit to either wrap all form input with addslashes() or mysql_real_escape_string();

 

Note, mysql_real_escape_string() requires an open database connection as I found out the hard way a couple days ago.

 

There is debate as to which is better and which one offers the most protection, but for the most part they are almost equal. From what I have read, mysql_real_escape_string() offers a bit more protection so I would probably get in the habit of using that one.

 

Nate

[dbo]

I'd also suggest verifying your data before escaping it. For example if your database is expecting an integer and you insert a character it is going to blow up even if you escape it.

[Kemik]

I've made several forms for this site I'm making, but this one just seems to be unstable. Sometimes it works, other times it doesn't.

 

I don't see why, I just copied and pasted from a previous form and changed the eregi rules I applied to allow spaces, . and -

 

For now, it's working as far as I can see. If anyone wants to take a crack at breaking it I'd be happy to give you access (it's in an admin only area). If it breaks while using it when my site goes live I'll just rewrite the whole structure and start this one form a fresh. I'd copy and paste the form code here but it's over several files because of functions I use.