I'm using an application linked to a database which holds confidential data (numbers). According to the developer, this data is encrypted. However, i can easily generate a report that will show the numbers in clear text. How can the application access and get this data if they're encrypted? And if the data is accessed by the application, what's the benefits of the encryption in the first place? The attack can be initiated starting from the web app in that case which will overcome the encryption ...
This is a precaution against (certain forms of) SQL injection or anyone who can access the database otherwise (internal breach on your database server). The application will do something like this:
Fetch information from DB ==> use decrypt function on information ==> present to the user
Whereas when using SQL injection, you will directly use queries injected to extract the data. However because you cannot call the decrypt function (because that's part of the application's code) used by the application you can only get encrypted data.
This might not be true for all situations where you can use SQL injection. If a function is vulnerable for SQL injection where there is code which loops through an array of results and decrypts that information and you are able to modify the results which are gathered by the function (by for instance injection a
1=1 statement). You will get those results returned to you, decrypted.
Note that database encryption is just a part of security measures to harden data-driven applications. You can also have a look at this paper by RSA.
Encryption is about ensuring confidentiality. Encryption does not ensure confidentiality absolutely, but it translates the problem to the key: when data is encrypted, it will be readable only by entities who have access to the decryption key. The key needs not be stored in the same place as the data itself, and that's the kind of separation which we are talking about here. Namely, encrypted data is in the database but the decryption key is in the application, which can be a distinct machine -- so this offers protection against attackers who can get a look at the database server, but not at the application machine.
As @Lucas points out, this works against some forms of SQL injection as long as the decryption is not doable in SQL. Some databases have abilities to perform the cryptography themselves, so SQL injection attacks may leverage these abilities. As an extreme case, consider what Microsoft calls Transparent Data Encryption in SQL Server: all the data is encrypted, but this is done automatically by the database server itself; the application is not even aware of it. This offers zero (nil, void, nada) protection against SQL injections and other application-level hacks. TDE is only against people who read the storage medium without going through the SQL Server code.
An attacker who hijacks the application machine will be able to read the whole database and decrypt it entirely, so encryption cannot protect against that. In fact, there are relatively few plausible scenario where database encryption actually offers extra protection (as opposed to password hashing, which is one-way, and which does offer a lot of extra protection as a second line of defence).