is it safe to have a big table in database?

Question

My client's site has a 'online evaluation' section, which consists of a 40 field form. Each field requires user input which I have program to allow from 50 to 200 characters depending on the question.

Then the form is sent to client's mail, but it is also stored in a database so client can check all the forms later.

I'm using codeigniter, and I used active records to prevent sql injections. Also I spambot-validated it with a method i found posted in another question. I have to hidden fields, one hidden with css. If values are filled or do not match then the form does not validate. (Btw, is this enough or should I also add a captcha?).

the code is something like this:

<p class="mail"><input type="text" name="mail" id="mail" /></p> //then I hyde this with css
<input type="hidden" name="mail" value="some value" />

and then i validate it

if( $this->input->post('mail', true) != '' AND $this->input->post('mail', true) != 'some value') 
{
    die('could not send your request');
}
else
{
    process form
}

My question is, when creating the database table since i added 42 fields I dont know if it was my pc, but my internet connection really slowed down and almost crushes. I'm worried that maybe an attacker could try to fill this form several times and filling each field completely, and then database collapses? Is this posible?

Is it safer to have that table divided in maybe two or three different tables? Or is that pointless? I dont know much about security, I'm trying to find places where to learn, but all the info I find is full of terminology I don't really understand, so it's quite hard. I appreaciate your help very much.

edit: what would be the correct way of doing it?

`is it safe to have a big table in database?` ironic. A database is to store data. — Dave Chen, Jun 16 '13 at 22:10
so it's okay to have a really big table? if it is filled several times there's no way it might collapse? — Ant100, Jun 16 '13 at 22:11
I have a database with a table called logs, I've never cleared it, it has around a few million records. — Dave Chen, Jun 16 '13 at 22:13
I mean several times in little time, as in a way of attacking? — Ant100, Jun 16 '13 at 22:14
I'm very sorry if I'm asking dumb things, like i said i dont know much about security and all places i find seem incredible confusing for begginers. Thank you all for your comments. So I shouldnt worry about this? — Ant100, Jun 16 '13 at 22:16
*assuming* its not mission critical, i would not worry, until something became a problem — , Jun 16 '13 at 22:17
It's not an issue. Unless the way in which you are using PHP to input the data. Remember to move database queries outside of loops and only use one database connection per request if the page requires it. — Dave Chen, Jun 16 '13 at 22:17
made my day. I totally agree, but just because you can't do it, doesn't mean you shouldn't fix something that someone else could do. — Dave Chen, Jun 16 '13 at 22:18
when you dig in to hacking websites you find terms and names and methods that people use and that will teach something about security — Gunr Jesra, Jun 16 '13 at 22:18

score 6 · Accepted Answer · answered Jun 16 '13 at 22:15

6

A database is made for storing data.

Of course every system has limitations, but also with a single machine and a classical relational database, the fast majority of all projects have no problems performance wise.

Breaking up into multiple tables makes no sense.

From a performance point of view just make sure that you have your indexes right for your queries. It sounds like text fields would be a good choice for your schema, since you dont need to index the answers and they can vary in length.

Of course you can use captchas, but generally I would recommend only employing it if you in fact do have spam problems.

The honeypot practice you described is, at least for now, a good one.

Also no matter how slow your connection, I do not think that transmitting that little text will fill it up.

I hope that answers all obvious parts of that question. If you need more informaiton, please describe your exact problem a bit more precise and give more information about your system setup.

answered Jun 16 '13 at 22:15

The Surrican

29,118
24
122
168

1

"Breaking up into multiple tables makes no sense." --- well it makes sense from a performance point of view. – zerkms Jun 16 '13 at 22:19
so is it better to seperate it if its too big? @zerkms – Gunr Jesra Jun 16 '13 at 22:20
@Gunr Jesra: there is no a silver bullet answer for that, but partitioning/sharding is a quite common technique when you have **A LOT** of data – zerkms Jun 16 '13 at 22:21
Oh I should use text fields? I was using varchar. Yes you've pretty much answered all my doubts, I appreaciate the time taken. Thanks :) – Ant100 Jun 16 '13 at 22:22
1

"I should use text fields" --- actually, no. mysql handles `text` worse than `varchar` – zerkms Jun 16 '13 at 22:23
@zerkms I stick to varchar then – Ant100 Jun 16 '13 at 22:25
of course sharding makes sense on a web scale. for some use cases also in a local machine, mysql has a feature for that called `partitioning` but i dont think this is relevant to the questioner – The Surrican Jun 16 '13 at 23:01

TheEnvironmentalist · Answer 2 · 2013-06-17T01:34:09.703

1

Don't increase the complexity of your information storage system to accommodate the servers. The KISS principle is something to be well aware of. Databases are designed to store information, and in some web applications, there are absolutely huge databases. As far as security, assuming you've got the obvious down, you should be fine. If you really want to you can add the timestamp and IP address to the list of stored information (if they weren't on there already, which for security and debugging reasons, often should be), and then put in a function which checks how many entries have been recently entered by that IP address and locks someone out if they pass a certain threshold, and if and only if you actually have experienced problems with spam you can add in captcha, but that tends to decrease user satisfaction. Considering that some applications upload images, which can easily be in the tens of megabytes, that's millions of characters, to databases, though at that point it becomes more efficient to store them elsewhere in the server, storing large amounts of text data in a single table shouldn't be an issue.

edited Jun 17 '13 at 01:34

answered Jun 16 '13 at 23:11

TheEnvironmentalist

2,694
2
19
46

So when it comes to images I should store them on server not database. I'll look more into the timestamp and ip address (first I'll have to learn how I can track user's ip address and store it, heh) and then implement the logic you just said. Thank you very much! – Ant100 Jun 16 '13 at 23:26
1

@Ant100 ip !=user, if you want to track users, you need a proper log in system – Jun 16 '13 at 23:38
@Dagon I see. Hmm... then what is theEnvironmentalist referring to? – Ant100 Jun 16 '13 at 23:53
As for the images, yes, because database storage is often more costly in a number of ways than storage directly on the server, you should put the images, indexed, on the server, and then put the paths to the files on the database. – TheEnvironmentalist Jun 17 '13 at 01:47
1

To get the IP address of a user, you might find [this link](http://stackoverflow.com/questions/1634782/what-is-the-most-accurate-way-to-retrieve-a-users-correct-ip-address-in-php) helpful. – TheEnvironmentalist Jun 17 '13 at 01:57
in order to do that, user must be login? – Ant100 Jun 17 '13 at 03:56

is it safe to have a big table in database?

2 Answers2