Junk Data in MYSQL DATABSE

[chriszd]

I have put up around 4 websites successfully with no problems at all.

When registering some fields need to be inputted otherwise no database insertion will occur. Also i generate a random number and if the user types it wrong, the registration form is stopped.

Last few days i noticed that i have junk data in my MSQL DATABASE and some fields which needed to be filled where left blank. If this was done directly from the website the registration would have stopped thus not letting any data to enter the database.

I am suspecting that the data is being written directly into the MYSQL, rather then from the registration page of the website.

What could this be? and how can i fix it?

 

[jpadie]

you mention msql and mysql separately. is this just a typo?

are the column validations done at the php level AND the database layer? or are you relying only on php validation?

as a first step at seeking the source of the data writes, examine the mysql bin logs.

 

[vacunita]

I am suspecting that the data is being written directly into the MYSQL, rather then from the registration page of the website.

How are you validating? Are you inserting to the DB in the same page that validates? If not how are you getting the values across?

Maybe taking a look at your code can help us pin point the problem?

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[DonQuichote]

Suspicions bring you nowhere. Switch on MySQL's querylog and see what happens. Also, if you don't have error logging, now is a good time to add it. If you do log errors, it may be a good idea to log the full contents of the superglobals also whenever an error occurs. Be careful that on some systems the GLOBALS array contains the other superglobals and itself. So you'd have to do some filtering to prevent infinite logging loops. Put an extra check near the INSERT query code. Check for known injection patterns and escape them properly, or reject them.

Also, check if error echo is turned off on the server. Otherwise, unhandled errors show up in the page, which makes hacking your site fruitful (the hacker then sees table and column names in the error messages).

Once you have the SQL query log, you can search it for known injection patterns: comments (like "-- "), always-true-conditions ("1=1") and so on.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[chriszd]

The registration of the website works this way:

Client clicks on Register and needs to fill some specific fields to be able to continue. Also the client needs to insert a random verification code given to him on registration. Once all are set data will be inserted into the database.
(THIS IS DONE BY USING THE $_POST METHOD)

The junk data i am finding has some required fields blank. This is why i am thinking that data is being inserted directly into the MySQL rather than from the website.
-----------------------------------------------------------

The msql was a typo sorry.

---------------------------------------------------------
Im not that much of an expert re the phpMyAdmin and SQL and how to check logs and prevent sql injections...

I sent an e-mail to the person who takes care of the hosting for me and hopefully i will get a reply back re the problem.

 

[ingresman]

does your code have any inserts any where else that might get called?
I'd follow donquichote's advice if I were you

 

[vacunita]

Client clicks on Register and needs to fill some specific fields to be able to continue. Also the client needs to insert a random verification code given to him on registration. Once all are set data will be inserted into the database.
(THIS IS DONE BY USING THE $_POST METHOD)

Is this all done in the same PHP page? The Validation, and the Insertion? Or does the insertion occur somewhere else?

Could we look at your code?


----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[jpadie]

i'd also like the answer to my questions.

 

[MichealC4]

I'm confused. Is there junk data being inserted in to MySQL or is nothing being inserted at all? The first sounds like either validation (which if you are using straight $_POST, that's begging for SQL injection and XSS ...) or logic, but if the second, that sounds like a purely logic problem.

----------------------------
"Will work for bandwidth" - Thinkgeek T-shirt

 

[DonQuichote]

Im not that much of an expert re the phpMyAdmin and SQL and how to check logs and prevent sql injections...

Then this is the first thing to repair. If you do a net search for SQL injection, you will find enough examples on what they are and how to prevent them. My guess is that your site is abused that way. They can only use your database directly if:
[ol]
[li] your database port is (unsecured) open to the internet (bad idea),[/li]
[li] unsecured shell access is open to the internet,[/li]
[li] web admin tools are present and insufficiently secured,[/li]
[li] or attackers had a chance to upload scripts.[/li]
[/ol]

As you mention phpMyAdmin, this may the attack base. However, if you see what's wrong in the database, this is very unlikely. If I were an attacker, I'd mask my actions as much as possible, editing the database only in a way that it at least looks like it could have been done through the web site.

The MySQL query log is a featrure of MySQL. In my.ini or my.cnf, set the setting:

log = [i](full path to where you want the log to be created)[/i]

.

Restart the Mysql service to make it effective. All the queries that are sent to the database are now logged. Be careful to rotate the logs once in a while or to disable it when you don't need the logging anymore.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[MichealC4]

In addition to to what DonQuichote suggested, I'd like to see what the junk data is, as well as your code as jpadie asked.

----------------------------
"Will work for bandwidth" - Thinkgeek T-shirt

 

[chriszd]

This is the code for inserting and checking for empty fields:

$username = $_POST['username'];
$password = $_POST['password'];
$name = $_POST['name'];
$surname = $_POST['surname'];
$tel = $_POST['tel'];
$mob = $_POST['mob'];
$email = $_POST['email'];
$dobday = $_POST['dobday'];
$dobmonth = $_POST['dobmonth'];
$dobyear = $_POST['dobyear'];
$idcard = $_POST['idcard'];
$gender = $_POST['gender'];
$address = $_POST['address'];
$location = $_POST['location'];
$allergies = $_POST['allergies'];
$aboutus = $_POST['aboutus'];
$numcheck = $_POST['numcheck'];
$validationnumber = $_POST['validationnumber'];



//check verification doe
if($numcheck != $validationnumber)
{
header("location:register.php?ErrorRegister=<strong> Sorry but the verification number is not valid </strong><a href=JavaScript:history.back();> Click here to change the Details</a>");
mysql_close();
}

elseif ($numcheck == $validationnumber)
{


//connect to dayabase

//CHECK IF ID CARD USERNAME OR EMAIL ARE ALREADY IN DATABASE
$check = mysql_query("SELECT * FROM clientstable WHERE idcard = \"$idcard\" OR username = \"$username\" OR email =\"$email\" ");
$returned = mysql_fetch_array($check);


//if a user with the same idcard, username or e-mail is returned we redirect the register page
if(!empty($returned))
{

header("location:register.php?ErrorRegister=We are Sorry <strong>$bookingname,$bookingsurname</strong> but there is already a user with the same ID CARD No <strong> $idcard </strong>, with the same USERNAME <strong> $username </strong> or with the same E-MAIL <strong> $email - </strong><a href=JavaScript:history.back();> Click here to change the Details</a>");
mysql_close();
}

else

{

//connect to database

mysql_query("INSERT INTO clientstable (username, password, name, surname, tel, mob, email, dobday, dobmonth, dobyear, idcard, gender, address, location, allergies, aboutus) VALUES('".$_POST['username']."', '".$_POST['password']."', '".$_POST['name']."', '".$_POST['surname']."', '".$_POST['tel']."', '".$_POST['mob']."', '".$_POST['email']."', '".$_POST['dobday']."', '".$_POST['dobmonth']."', '".$_POST['dobyear']."', '".$_POST['idcard']."', '".$_POST['gender']."', '".$_POST['address']."', '".$_POST['location']."', '".$_POST['allergies']."', '".$_POST['aboutus']."')")
or die(mysql_error());

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

Junk data:

Address filed:
<a href="

http://www12.asphost4free.com/samsung10/samsung-u740-alias.html

">samsung u740 alias</a> <a href="

http://www12.asphost4free.com/samsung10/verizon-no-contract-samsung-alias-phones.html

">verizon no contract samsung alias phones</a> <a href="

http://www12.asphost4free.com/samsung10/verizon-samsung-alias-cell-phone-reviews.html

">verizon samsung alias cell phone reviews</a> <a href="

http://www12.asphost4free.com/samsung10/verizon-wireless-samsung-alias.html

">verizon wireless samsung alias</a> <a href="

http://www12.asphost4free.com/samsung10/video-format-samsung-u-740-alias.html

">video format samsung u-740 alias</a> <a href="

http://www12.asphost4free.com/samsung10/samsung-i900.html

">samsung i900</a> <a href="

http://www12.asphost4free.com/samsung10/samsung-i900-omnia.html

">samsung i900 omnia</a> <a href="

http://www12.asphost4free.com/samsung10/samsung-omnia-i900-sim-free.html

">samsung omnia i900 sim free</a> <a href="

http://www12.asphost4free.com/samsung10/is-samsung-i

Name: molotov
Surname: molotov
Tel: tctDOTdqWUbZp
username: molotov
email: molotov@gmail.com
password : (is left blank)
location: (is left blank - the loction is selected from a drop down menu, in which on registration from the website, a value will be selected automatically, thus being left blank in the junk data)

I have much more data like this, around other 30's. Im not pretty sure what's causing this problem, and since i don't have direct access to the servers, im still waiting for an answer from the person with whom i host the website.

I will try and search the net for the sql injections and how to prevent them. thanks and hope the info i gave you can give you a better picture.

 

[jpadie]

that is sql injection or form scraping.

you are doing no validation of client generated data. you are also not escaping your data. it is hardly surprising that your site is being abused.

* test each item of data to make sure it is of the type, length and content that you expect.
* escape the data religiously to ensure that it is properly submitted into the database.

 

[DonQuichote]

Brilliant! So If I submit anything with numcheck equal to the validation number (such as both of them empty!), I'm in. That is, if I try only a very basic SQL injection to get past the login.

There are two lessons to be learned:
NEVER SEND THE CHECK CODE ITSELF IN A HIDDEN FIELD! From your code, I assume you do that. Anyone can alter the contents of a hidden field and post that. It is really extremely simple. Instead, keep the check codes in the session and NEVER send them to the client.

NEVER TRUST USER INPUT. You do not take any precautions to make sure your SQL is valid: you don't use prepared statements and do no escaping at all. In other words: your site is really simple to hack.

Oh, and a last warning: never rely on the automagic quotes to be on. When you upgrade PHP, the default is OFF and starting with PHP6 the option is removed at all (thank goodness. Far too many starting programmers wonder why the site input is corrupted).

And off course I suggest reading a good book on website security for programmers...


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[vacunita]

As jpadie and Don have pointed out wow!. Totally unsecure form.

Not much too add other than, escape your inputs.
I would also check for empty fields. If you need something to be filled in then explicitly check for them.

Additionally , this does not really affect the abuse of your form, but its just rather pointless:
Is you have all these variable assignments, of which you only use 1 the numcheck and the validationnumber.

Your insert statement uses the $_POST variables directly again a bad idea.

Take a look at this it might be enlightening:

http://en.wikipedia.org/wiki/SQL_injection

Also I'd point you to mysql_real_escape_string:

http://php.net/manual/en/function.mysql-real-escape-string.php

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[MichealC4]

Looks like I got beat. I'd also recommend picking up a couple of PHP programming books that focus on security. You are also vulnerable to XSS, which is very, very serious.

----------------------------
"Will work for bandwidth" - Thinkgeek T-shirt

 

[ingresman]

those issues aside, does that account for the junk data ?

 

[MichealC4]

No validation.

----------------------------
"Will work for bandwidth" - Thinkgeek T-shirt

 

[chriszd]

Thanks guys, i will check some websites re this issue as i never encountered anything liek this before and don't want anything more serious to happen.


I do have validation on some fileds, using javascript ( like the ones used in dreamweaver, spry text filed )

 

[MichealC4]

As we say in the security community, never trust the client. JavaScript validation is easily defeated.

Also, this code is dangerous, in addition to your SQL queries:

if($numcheck != $validationnumber)
{
header("location:register.php?ErrorRegister=<strong> Sorry but the verification number is not valid </strong><a href=JavaScript:history.back();> Click here to change the Details</a>");
mysql_close();
}

This code, along with the ones like it, allow for XSS. An attacker can leverage this to make Bad Things(tm) happen to your visitors.

----------------------------
"Will work for bandwidth" - Thinkgeek T-shirt