Tag Info

Hot answers tagged

192

You can query data in a database (ask it questions). You can look up data from a database relatively rapidly. You can relate data from two different tables together using JOINs. You can create meaningful reports from data in a database. Your data has a built-in structure to it. Information of a given type is always stored only once. Databases are ACID. ...


185

First up, you should be more free with read-only access rights than read-write. It might be possible that a hacker has access to your data but isn't able to edit it. But, much more importantly, this is not about you. The fact that you might be screwed if someone has full access to your database is irrelevant. Much more important is your user's data. If you ...


157

Schema Changes Fetch by order --- If the code is fetching column # as the way to get the data, a change in the schema will cause the column numbers to readjust. This will mess up the application and bad things will happen. Fetch by name --- If the code is fetching column by name such as foo, and another table in the query adds a column foo, the way this ...


145

Whilst I agree with everything Robert said, he didn't tell you when you should use a database as opposed to just saving the data to disk. So take this in addition to what Robert said about scalability, reliability, fault tolerance, etc. For when to use a RDBMS, here are some points to consider: You have relational data, i.e. you have a customer who ...


63

If you get hacked you can restore the site from backups and fix it. But the hacker still has passwords for everyone's accounts! There are documented real world examples of this happening (Sony, Linked-in), where if the password tables had been properly hashed and salted, securing and restoring the sevice quickly would have been much easier. It's ...


59

In many cases, this is a bad idea. It will bloat the database files and cause several performance issues. If you stick the blobs in a table with a large number of columns it's even worse. However! Some databases, like SQL Server have a FILESTREAM column type. In this case, your data is actually stored in a separate file on the database server and only ...


58

Think about what you're getting back, and how you bind those to variables in your code. Now think what happens when someone updates the table schema to add (or remove) a column, even one you're not directly using. Using select * when you're typing queries by hand is fine, not when you're writing queries for code.


37

It's safe, if that's what you're asking. As long as you're as careful about your security as you are with your data's security. But don't reinvent the wheel, Stored Procedures ARE bits of SQL stored in a table. And they support, nay encourage, parameterisation. Also note, you can make your security simpler AND reduce the number of points of failure AND ...


36

One thing that no one seems to have mentioned is indexing of records. Your approach is fine at the moment, and I assume that you have a very small data set and very few people accessing it. As you get more complex, you're actually creating a database. Whatever you want to call it, a database is just a set of records stored to disk. Whether you're creating ...


35

But I have other problems if someone gets in my database, i.e. deleting data. It's not about the problems you have, it's about the problems it might cause for all your other users. It's about removing temptation (or even worse, potential liability) for people working on the site to abuse data that's stored there. See, even though people should use ...


33

I would add as an initial disclaimer that when you say DB and this question has tags indicating several platforms, that we are talking about a traditional relation database - i.e. a well-defined system which manages data in tables, columns and rows, according to Codd et al. This is a well-defined paradigm with well-understood boundaries. If you are talking ...


33

Another concern: if it's a JOIN query and you're retrieving query results into an associative array (as could be the case in PHP), it's bug-prone. The thing is that if table foo has columns id and name if table bar has columns id and address, and in your code you are using SELECT * FROM foo JOIN bar ON foo.id = bar.id guess what happens when ...


31

Advice: Don't be afraid of learning new things - you made a good First Step in acknowledging that you could do better and then made the effort to learn how you could do better. Yes, it takes more time up front, but the payoff is usually worth it in the long run. Now that you know CodeIgniter, you can use it for the next future project(s). You can put it on ...


28

There are two different kinds of pitfalls, those from using MySQL as your flavor of RDBMS and those of using an RDBMS instead of other types of databases. Using MySQL instead of alternate RDBMS's: See this wikipedia comparison table for various comparisons of MySQL to other RDBMS's. You will very likely prefer Microsoft SQL Server if you are building an ...


28

Reasons in favor of storing files in the database: ACID consistency including a rollback of an update which is complicated when the files are stored outside the database. This isn't to be glossed over lightly. Having the files and database in sync and able to participate in transactions can be very useful. Files go with the database and cannot be orphaned ...


26

PHP deliberately makes it really, really easy for people who know very little to create useful dynamic web pages. This means that PHP is going to attract a lot of beginners, who create something useful, learn from other useful looking examples, and turn around to teach others how to do this cool, useful thing. The result is a lot of bad code, and a supply ...


21

Querying every column might be perfectly legitimate, in many cases. Always querying every column isn't. It's more work for your database engine, which has to go off and rummage around its internal metadata to work out which columns it needs to deal with before it can get on with the real business of actually getting the data and sending it back to you. ...


20

Yes, it is a bad practice. Performance impact on the DB: if you do a SELECT with any BLOB column, you will always do a disk access, while without BLOBs you have a chance to get data straight from RAM (high throughput DB will be optimized to fit tables in RAM); replication will be slow, replication delay high, as it will have to push BLOB to slaves. High ...


19

Noticeable attacks like deleting data are usually the stuff of amateurs, and are the least of your worries. One of the first things an experienced attacker will do is attempt to gain legitimate access, so even if you patch the original vulnerability he used, he will still be able to get in. He will do everything possible to avoid drawing attention to ...


18

Possibly because at one point the code looked like this: $sql = "SELECT …"; echo $sql; //why is this <censored> query not working??????? mysql_query($sql); Otherwise, there's no compelling technical reason to go one way or the other. I personally find using the temporary variable makes the code a tad bit easier to maintain if down the road you ...


18

BTree (in fact B*Tree) is an efficient ordered key-value map. that means that a BTREE index can quickly find a record given the key, and it can be scanned in order. It also makes easy to fetch all keys (and records) within a range: "all events between 9am and 5pm", "last names starting with 'R'" RTREE is an 'spatial index' that means that it can quickly ...


18

It is OK to make a web application using C++ IF the benefits outweighs the cost, obviously. Google, Amazon, Facebook are all built with C++ for efficiency in speed, memory and energy - aka servers costs. However as you guessed, there are drawbacks to using C++ for this. It depends on your tools though. First let me cite cppcms website on this: When ...


17

I have to post an answer here on a fallacy in the question itself. You are asking if passwords should be encrypted. No one encrypts passwords; no one, with the exception of services and programs like Firefox Sync and Roboform, whose sole purpose is to encrypt passwords. Let's take a look at the definitions: In cryptography, encryption is the process of ...


16

To achieve highly organized and decoupled modularity, you can follow the Hierarchical MVC architectural pattern, sometimes known as Presentation–abstraction–control (although they aren't strictly the same pattern). Kohana, Alloy, Fluency and FuelPHP support HMVC natively* and Kohana's HMVC approach is discussed in Scaling Web Applications with HMVC and ...


16

MySQL timestamps: Are stored in UTC They are converted to UTC on storage and converted back to your time zone on retrieval. If you change time zone settings, the retrieved values also change. Can be automatically initialised and updated You can set their default value and / or auto update value to CURRENT_TIMESTAMP Have a range of 1970-01-01 00:00:01 UTC ...


15

Foreign keys should be enforced where it makes sense to enforce them. If a column must refer to something else then it should have a foreign key restriction. If the column might refer to something, but the business model suggests that it is not always the case, then it is acceptable to have a null in there, and have the foreign key present to ensure that, ...


14

With distributed SCMs like git or mercurial (which is what bitbucket uses), branching is still possible but often unnecessary. What you do instead is (basic workflow): Set up a repository in a location that is accessible for all developers (a network share, a server that people can ssh into, a third-party service like github or bitbucket). This will be ...


14

The calm approach that you suggest would be best. Pointing out that when this data gets exposed, most of your users will be vulnerable to identity theft due to password reuse. This would be a pretty good time to point out that this is the same issue that affected Target (assuming that the company isn't Target). And your manager should be pretty receptive ...


13

You're trying to denormalise separate facts into a single record, rather than storing them as individual records. Don't. You appear to believe that it will be easier and more efficient to do parsing of pseudo-sets yourself than to store them in the normal way. That is possible but unlikely unless you actually tried it out and determined that your database ...



Only top voted, non community-wiki answers of a minimum length are eligible