mySQL Question

[N-Bomb(Nerd)]

#1 - Would submitting my values with the mysql_real_escape_string() function be enough to protect me against mySQL Injection?

 

#2 -

 

My database is setup using (Name, Comment, IPAddress) for the values. I'm allowing the same people to submit multiple comments, as long as the comments aren't the same. I tried using:

if ($Comment != $Query['Comment']) {
    // add comment to db
}

 

It appears though that if a user has two or more comments that method won't work, what other approach do you suggest I take?

 

Thanks,

N

[N-Bomb(Nerd)]

Sorry, was in a rush when I was typing that.. if any of it isn't clear let me know and I'll do my best to try and explain it.

 

Thanks.

[Mchl]

#1. mysql_real_escape_string() is an absolute minimum that will protect you from most common injections. There are some more things you should take care of.

Read this: http://www.webappsec.org/projects/articles/091007.shtml

 

#2. That is unclear.

[gizmola]

For #1, the recommended way of avoiding SQL injection is to use prepared statements and bind variables.  The second best solution is mysql_real_escape_string.  One thing to watch out for is that mysql_real_escape_string is like a hammer -- it will *fix* binary data just as easily as it will fix text, and in some cases that could cause problems.  So if you need to, for example, compare md5() hashed data with stored md5 passwords, or to store binary data (pictures perhaps)  then you should not run mysql_real_escape_string on that data before you hash it.  The hash process itself is immune to any issues, since it transforms the input.

 

For #2, we don't really have enough info.  If you're searching the comments table for a match, then the best solution is to do SELECT count(*) as countof FROM table WHERE IPAdress = "$ip" AND comment = "$thecomment$".

 

You will always get a result, and the result will only be an integer.  On anything > 0, you don't do the insert. 

[N-Bomb(Nerd)]

Thanks for the replies on #1, I'll be sure to follow that link and have a nice read.

 

I can't really think of any good ways to explain #2 so I'm going to write it all like the script would read it.. if it could talk english.

 

Database:

-------------------

Jim Jones lol nice 123.123.123.123

Gary Keph goodjob 321.321.321.321

Jim Jones amazing 123.123.123.123

-------------------

 

 

Now when "Jim Jones" goes to submit another comment I want my script to check if his comment already exists.

 

Example:

 

If "Jim Jones" tried to submit a comment that said "your really good dude".. my script would check to see if that comment already exists for the name "Jim Jones".

 

If the comment already exists for the person with the name "Jim Jones", it won't add the new comment to the database. However, if the comment doesn't exist for the name "Jim Jones" it's going to add his new comment to the database.

 

Jim Jones says: "amazing" - it won't add because it's already in the database under the name "Jim Jones"

Jim Jones says: "clearly pro" - it will add the comment to the database because "Jim Jones" hasn't said this yet..

 

Gary Keph says: "amazing" - this will add to the database under the name "Gary Keph" because he hasn't said "amazing" yet.. even though "amazing" already exists under Jim it will still add for Gary.

 

 

However, my problem with the code posted above is that once a user submits more than one comment I can't figure out how to check the current comment verse all of his comments in the database.

 

Hope this helps clear #2 up.. chances are it will probably just confuse you more.

[N-Bomb(Nerd)]

Please if #2 is still confusing let me know, and I'll try to explain it better.

 

Thanks,

N

[N-Bomb(Nerd)]

Sorry for bumping, but my project is on hold until I can get this figured out.

 

Thanks,

N

[Mchl]

Basically what you're doing doesn't have much sense IMHO. Instead of blocking the possibility to send same content twice, you should block the ability to post one comment after another I think...

[N-Bomb(Nerd)]

Basically what you're doing doesn't have much sense IMHO. Instead of blocking the possibility to send same content twice, you should block the ability to post one comment after another I think...

 

It makes perfect sense to me, I guess I just have a weird way of doing things, but the method I'm trying to figure out is one of the my main ways to cut spam and is needed.

 

This script I'm making now is going to attract a lot of spam, and I actually plan to also include a delay after each comment made. Also, I'm restricting each ip to a set amount of comments per day to also help reduce spam.

 

Mainly though I want to stop users from spamming the same comments over and over.

[Mchl]

Create a UNIQUE index on the columns you want to be unique then. MySQL will do the rest for you. You'll just have to catch MySQL error #1062 (I think) to display information for user.

[N-Bomb(Nerd)]

Not quite sure what you mean.. but here's how I'm stuck.

$Query = mysql_fetch_array(mysql_query("SELECT * FROM `test` WHERE `Name` = '" . $Name . "'")); 

if ($Query['Comment'] != $Comment) {
    mysql_query("INSERT INTO test (Name, Comment, IPAddress) VALUES('" . $Name . "', '" . $Comment . "', '" . $IPAddress . "')");
}

 

However the database might look like:

Jon watching 123.123.123.123

Jon say what 123.123.123.123

Jon haha nb  123.123.123.123

 

So in the above code "$Query['Comment']" is going to be the last result in the database in this case "haha nb  ". So in the above code "$Comment" could be "say what", and get submitted into the database again, because for some reason I can only get the last result with "$Query['Comment']".. whereas I need to check "$Comment" verse all the comments for Jon.

[Mchl]

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

 

take a look at UNIQUE

[gizmola]

Did you read my answer?  I already provided a solution. 

 

Mchl has a good point in regards to unique indexes, however, there is a limit to the size of an index that you can create on text, and I personally would probably go a different way.  I think a fair compromise is to use the IP address, which will only really become a problem if you have people who leave lots and lots of comments, using the same IP.  I think this could be mitigated if you added a timestamp column to the table which was indexed.  IP Address should also be indexed.  Neither of these indexes will be unique, but together they will help restrict your result set to a manageable size.

 

Then you could do a query which only attempted to check for comments that are the same for that IP and the previous 5 minutes, which would insure that you maintain good performance as time goes on and your database fills with comments. 

 

This blog post covers mysql date arithmetic: http://www.gizmola.com/blog/archives/51-Exploring-Mysql-CURDATE-and-NOW.-The-same-but-different..html