Tag Info

Hot answers tagged

196

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


166

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


66

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


63

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.


60

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


37

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


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


37

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


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.


32

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


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


25

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


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


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

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.


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

It's actually quite an easy choice. Right now, you have zero users, and scalability is not a problem. Ideally, you want to reach the point where you have millions of users, and scalability becomes a problem. Right now, you don't have a scalability problem; you have a number-of-users problem. If you work on the scalability problem, you will not fix the ...


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

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


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



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