Skip navigation

Tag Archives: SQL

Well, so my requirement this time was to get the size of all tables in a particular schema. HOW?

AFAIK, there was no direct way to fulfill my requirement. So wrote my own query..

SELECT pg_size_pretty(sum(pg_total_relation_size(‘archival.’||table_name))::bigint)
FROM information_schema.tables
WHERE table_schema=’archival’;

Where ‘archival’ is the schema name. Checkout the type-casting.
Short explanation: sum() returns int type, but pg_size_pretty() takes bigint as the param data type. Hence the type casting.

NB:- when copying the query, take care of the quotes.

Getting Database size:
SELECT pg_database_size(‘user’);
pg_database_size
——————
58241934

Oh.. thats in Bytes.. Make it nicer.. Get db size in MBs.

SELECT pg_size_pretty(pg_database_size(‘user_table’));
pg_size_pretty
—————-
60 MB

Getting Table size:

SELECT pg_size_pretty(pg_total_relation_size(‘user_table’));
pg_size_pretty
—————-
55 MB

Ok.. so the problem was taking a sql dump in PostgreSQL db of only selected entries in a particular table. Google’d it.. but all in vain.. then came up with a simple workaround…

First create a temporary table in the database as follows:

create table <temporary_table_name> as select * from <table_name> where <some condition>;



This will create a table.

Well.. thats it.. now u can take a dump of that table :-) and then delete it subsequently.


./pg_dump -U <username> -p <port> -d <db_name>
-t <temp_table_name> -f <file_name>

N.B. MySql has a ‘where’ option in mysqldump.. so its quite straight forward there..

Well well, here I am writing my first blog. Thanks to google, as always :-) !!!!
Firstly, a small intro abt me and the reason tht made me write about SQL injections.

I passed out my engineering(Info. Tech.) just this year, July’08 to be more specific. As soon as i was out from my coll i joined an IT firm as a developer in the .Net team. The first task i got was securing a web application which was hacked by “SQL injections”. The application was developed 7yrs back and was coded in classic ASP(VBScript). The application didn’t have any sort of user input validation.

I knew just the basics of what actually SQL injections were.. I did my research on SQL injections on google. Here‘s a nice intro on SQL injections and some methods to prevent it.

After going through the doc, i decided to get my hands dirty.
The First thing i did was basic input validation. Mind it, it was server side. A beginner might ask y server side and not client side?? Well, let me explain..

When u do client side validation. The data is validated only in the browser(client side). A hacker might just save the page on his local machine and remove the java script/vb script and then try to pass data. It wont get validated now!!! The hacker can easily inject malicious code(scripts) which will get stored in the data base. The script might be a worm or something which would infect the entire database.

The second thing was using PARAMETERIZED queries. This thing helps a lot. The SQL server does the job of validations. The same link gives you an intro on Parameterized queries, so i wont waste time on discussing it in-depth.

The third thing which i came up was using 2 seperate connections to access the database. One of the connection used an account with database owner(DBO) permissions, whereas the second connection used an account having read only permissions. So, the page which had insert/update queries used the DBO account whereas all the other pages used the less privileged account.
This prevents SQL injections big time. For e.g. the sql which only retries data from the db can be appended by DROP or UPDATE query, specially the user LOGIN pages.

Well, SQL injection does not ONLY take place by entering malicious sql in the HTML input fields, but can also take place by modifying the COOKIE content, also known as COOKIE HIJACKING. Cookies are often used to store username and other user preferences. The cookie values are then taken by the application to fire SQL queries to the database. So one has to validate the cookie contents too.

One more thing i noticed from the web application log was that the script which was referred by the script stored in the database was in HEXADECIMAL(all numbers) making things worse.

Ending note… : When developing an application, consider yourself as a hacker! :-)