Tag Info

Hot answers tagged

202

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. ...


192

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 ...


169

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 ...


147

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 ...


110

Unicode is certainly difficult, and the UTF-8 encoding has a couple of inconvenient properties. However, UTF-8 has become the de-facto standard encoding on the web, surpassing ASCII, Latin-1, UCS-2 and UTF-16. Just use UTF-8 everywhere. The most important reason why you should support Unicode is that you shouldn't make unnecessary assumptions about user ...


69

Before you lose any data, let me try to introduce a sysadmin perspective to this question. There is only one reason we create backups: to make it possible to restore when something goes wrong, as it invariably will. As such, a proper backup system has requirements that go far beyond what git can reasonably handle. Here are some of the issues I can foresee ...


65

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 ...


65

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.


61

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 ...


54

I think beyond the technical question, your boss may not have the time to keep up to date on current standards. Since his stance is not completely out to lunch, just out-dated, respect his position when discussing this matter (and you need to remember to discuss, not argue), and try to work through concerns he has with regards to UTF-8. I suspect the ...


46

Which of us is right? Once upon a time, your boss was. But as time goes by, things change. Nowadays, you are (but before running to your boss, be sure to read Nelson's answer too). Old versions of MySQL, and old versions of mostly everything, dealt much better with the older Latin1/ISO-8859-1(5) than UTF8. There is a reason why UTF8 has been created, ...


41

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 ...


39

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 ...


38

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 ...


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

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 ...


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 ...


34

My two cents: I do not think it is a good idea. GIT does something like "storing snapshots of a set of files at different points in time", so you can perfectly use GIT for something like that, but that doesn't mean you should. GIT is designed to store source code, so you would be missing most of its functionality, and you would be trading a lot of ...


32

I think it's mostly due to a) ignorance b) laziness. Beginners usually don't know much about sql injection, and even when they hear about it, they ignore it because it's so much simpler and easier to code that way.


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 ...


29

If you want to save SQL statements in a database for later execution, there's a better option than putting them in a table: use the built-in functionality provided for this specific purpose. Put them in stored procedures.


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 ...


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 ...


26

Never expose direct access to your database from the web layer. You can never lock that down; someone will send "DROP TABLE table" to your server, however much you lock down the JS source. All they have to do is see what the browser sends to your server to detect that your server accepts arbitrary SQL commands. Not that you can lock down the JS code; it is ...


23

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 ...


22

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. ...


21

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 ...


21

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 ...


20

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 ...


20

What you are going through sounds quite normal to me. This is how we work on our craft and get better and better at what we do.



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