Digital India : Can anyone read data from formatted memory card / phone ?

The reason for this post tagging against digital India is because smartphones, memory cards or pen drive usage is very high due to the population and digital exposure. Also, sale and usage of used phones is equally high compare to the new devices. Taking photos/videos is one of the prime use of our mobile phone nowadays and sometimes people do store very much personal photos/clips on their device. Later people delete all these information assuming that no one else can access during resell.

But does that really remove information from the memory card? NO – the way delete or format process works is just clearing mapping of that information. Means marking that space as free so that device software can rewrite the data on that location. So if the location is not reused after format/delete then anyone can easily recover this information.

On the internet, there are many software available which can help any novice person to recover information from the memory card/ hard drive. These generally come at the very much nominal price or some of them are free to use with easy steps. This software looks for the raw data on the card and joins them together like solving some jigsaw puzzle.

Does that mean we should not be storing sensitive information on the memory cards or other storage? Ideally Yes – but if we cannot resist then it’s better to store all such information in the separate memory card attached to the device. During resell we can just sell the device without the memory card or just destroy memory card. As memory cards are not very costly so should not be affecting your device’s reselling value.

Another way is to format your device then store some junk information ensuring all storage is used and format again. Doing this activity 2/3 times will ensure that original set of information is completely overwritten.

In a summary its individual’s responsibility to protect his/her personal data and how far he/she is going with respect to sharing personal information. All the best and happy new year.

Advertisements

Digital India : Is Google Tracking Your Andriod Phone ?

Today most of the mobile companies use Andriod as an operating system. The reason behind this that it is open source, user-friendly, robust, stables and saves the good amount of time in investment on building new OS from scratch. Any Andriod based phone provides a large number of apps via google app store. When individual signs into app store we also authorize access to the Gmail, calendars, and other google services by default. One of the hidden services is google location history. This tracks your location and stores on your Gmail location data. Those who have Android phones can search for “google location history”  and view their past location data date by date.

The functionality is very much effective with respect to tracking your device, but we can switch off location tracking if we want by going on to the below link

https://www.google.co.uk/maps/timeline

We can also delete historic data. More information on how to do it can be found on the below link

https://support.google.com/accounts/answer/3118687?source=gsearch&hl=en

The purpose of this post is just to provide more knowledge about the hidden features of the smartphones.

Digital India : Twitter Accounts hacking

Last month there was big noise for some of the known journalist’s twitter account getting hacked. After this, some people including victims started blaming the government for the same. Does that mean ruling government failed to implement digital India?

First twitter is private firm independent from any government and has its own strong security policies otherwise, hackers could have hacked every other account from twitter. Therefore its individual’s responsibility to secure own account information from hackers.

So what could be the reasons then? Sometimes by clicking unwanted links for free goodies, discounts, WhatsApp forwards, lottery winnings and more we authorize access to the personal information to unwanted peoples. Also, most of the people tend to write passwords physically on the notes. And most common typing passwords in presence of others or personally giving passwords details 🙂

Most the above reasons are very common but very much important, therefore its individual’s choice how much personal data he/she sharing knowing/unknowingly over the internet.

Oracle : Group By Clause

Aggregate functions are those functions which take one or more input values and provide single output value per chunk of input recordset. In SQL based applications/environment, Aggregate functions (MAX, MIN, AVG, SUM, COUNT) are mostly used. These functions are not only used for generating summarized reports but also to estimate datasets before building any query on the complex tables.

But in some of the cases we cannot use these functions directly e.g. we need to find total salary credited per employee for the given financial year. In this case, we can use SUM function by giving date time range, but that will give the total salary of all employees. We can give employee name in the select clause such as “SELECT EMPLOYEE_NAME, SUM(SALARY) FROM EMP WHRE PAY_DATE BETWEEN :A and :B “. When database tries to run this query it will select employee_name first and then execute second function finding the sum of all salaries. This won’t be correct from data integrity perspective as the database is displaying all salaries against each individual record. Therefore Database will give an error to enforce data integrity. In such cases, we can use group by clause. With the group by we are informing database to summarize output on the basis of the column as below for the above example.

SELECT EMPLOYEE_NAME, SUM(SALARY) FROM EMP

WHRE PAY_DATE BETWEEN :A and :B

Group by EMPLOYEE_NAME;

The simple thumb rule is whenever we are using any aggregate functions then columns on which no functions used must be included in the group by clause. Group by clause will always come after where clause and before the order by or having. Static values are not required to include in the group by clause. P.S. we cannot use any aggregate functions in the where clause.

Sometimes we need to filter output values e.g. Finding employees paid more that 100K in last year. We cannot apply additional filters at where clause since the sum of annual salaries not available. In order to find the result, we can make use of HAVING clause which will work on the output of aggregate functions. With Having clause, we can rewrite above query as below.

SELECT EMPLOYEE_NAME, SUM(SALARY) FROM EMP

WHRE PAY_DATE BETWEEN :A and :B

Group by EMPLOYEE_NAME

HAVING SUM(SALARY) > 100000;

In a summary group by clause is very good functionality provided by oracle and very much effective for filtering and grouping a large amount of data.

Oracle : Basic Tips for writing optimised SQL Queries

SQL queries are one of the core parts in every of the RDBMS-based applications. Most of the times a bad SQL query is the reason for performance and data integrity issues. So how can we build optimized SQL queries?

Every query is different from each other. Therefore we cannot follow the same approach for writing SQL queries. Some of the basic steps are listed below.

  • Understand the expectation of the problem and then analyze respective application tables, columns and relevant primary and foreign key mappings.
  • Always try to use indexed column/primary key in your query. If we do not have this information then you can consult application DBAs to get index information. Most of the times query engine we are using provides functionality for viewing table related information.
  • Avoid using hard parsing means the use of static values rather than passing bind variables. for e.g. in the simple scenario, the application is validating username without passing as bind variables. You can refer to the using bind variables blog for the detailed explanation. This sometimes slows down application performance if usage of the same SQL is high. It will also expose passed values in the SQL query.
  • Forecast growth of your key tables and use right index.
  • Avoid using big sub-queries inside “IN Clause”. In the case of fetching large data, database executes sub-query every time for each fetch. To avoid this use EXISTS clause.
  • For large tables avoid using like statements with % tag on both ends. If it is part of application design then suggest creating function index on the column where like clause is used.
  • Always give preference to direct joins and if you notice redundant information in the tables.

These are some the basic information we need to be aware of writing basic queries. For some people, it could simple, but during my IT career, I have experienced many times lack of usage of basic principles. I will be writing another post on usage of commits and group by clauses.

Java/Servlets : Use of JDBC for DB connection with bind parameters

Following is the simple program showing the implementation of connecting to Oracle DB using ODBC driver and fetching data with bind values.

Here DBUtilXE is the utility providing static connection to the Database and function to select data from Database. In below example, I am set fetch size to 1000 which can be lowered down to the smaller number when resultset is small. By default, this value is set t0 10, so in the case of higher return dataset application needs to make that many DB trips to get all data. Values highlighted in blue color are dummy values which need to be updated as per your environment setting.


public class DBUtilXE {

                private static Connection dbConn = null;

                private static PreparedStatement preparedStatement = null;

                private static ResultSet dbResultSet = null;

                private static String DBHost = “120.10.10.10“;

                private static String DBUser = “UserName“;

                private static String DBpass = “Password“;

                private static String DBSID = “XE“;

                private static String DBPort = “1521“;

                static {

                                try{

                                                Class.forName(“oracle.jdbc.driver.OracleDriver”);

                                                }catch(ClassNotFoundException e){

                                                                e.printStackTrace();

                                                }

                                try{

                                                dbConn=DriverManager.getConnection(“jdbc:oracle:thin:@”+ DBHost + “:” + DBPort +”:” + DBSID,DBUser,DBpass);

                                                }catch(SQLException e){

                                                                e.printStackTrace();

                                                }

                                }

                public static ResultSet selectDBRow(String sqlQuery, String[] inpSQLBindParameter ) {

                                try {

                                                if (sqlQuery != null) {

                                                                                  preparedStatement = dbConn.prepareStatement(sqlQuery);

                                                                                  preparedStatement.setFetchSize(1000);

                                                                                for (int i=0;i <inpSQLBindParameter.length;i++){

                                                                                ((PreparedStatement) preparedStatement).setString(i+1, inpSQLBindParameter[i]);

                                                                                }

                                                                                try {

                                                                                                dbResultSet = preparedStatement

                                                                                                                                .executeQuery();

                                                                                                return dbResultSet;

                                                                                } catch (Exception e) {

                                                                                                e.printStackTrace();

                                                                                                return null;

                                                                                }

                                                                } else {

                                                                                return null;

                                                                }

                                } catch (Exception e) {

                                                e.printStackTrace();

                                                return null;

                                }

                }

}


Further main class or any other class can use this class and fetch dataset from the Oracle DB for the given query. Here in the SQL Query I have used bind variables(?) which saves DB time in parsing and also help to hide input value during execution at Database.


,tring sqlQuery = null;

String[] strSQLBindVal ;

ResultSet resultSet = null;

strSQLBindVal = new String[2];

                                                                sqlQuery = “SELECT * FROM USERS WHERE USER_NAME = ? AND PASS_VAL = ? “;

                                                                strSQLBindVal[0] = “sushant“;

                                                                strSQLBindVal[1] = “passval“;

                                                                resultSet = DBUtilXE.selectDBRow(sqlQuery,strSQLBindVal);

                                                                try{

                                                                                if (resultSet != null ) {

                                                                                                while(resultSet.next() ){

                                                                                                                //Printing first column value for each record

                                                                                                                System.out.println(db2MFResuletSet.getString(1));

                                                                                                                //Printing second column value for each record

                                                                                                                System.out.println(db2MFResuletSet.getString(2));

                                                                                                                }

                                                                                                resultSet.close();

                                                                                                }

                                                                }catch(Exception e){

                                                                                e.printStackTrace();

                                                                }


In above explanation I am printing SQL output, this can also be stored into the Array Objects for application use.

Visual Studio : Storing and Retrieving Data from Registry

While creating desktop applications many times you need to save some application data in the registry. This removes application dependency on the files for configuration or one-off information. This code does not need any additional library to be added and the developer can create their own path for storing key-value pairs.

Saving Data in Windows Registry:- 

SaveSetting(Application Name, Section Name, Key , Value);

e.g . SaveSetting(“simpleDesktopApplication”,”Configuration Item”, “UserName”,”Sushant”);

Retrieving Data from Windows Registry:- 

reutrnValue = GetSetting (Application Name, Section Name, Key);

e.g . strUserName = GetSetting (“simpleDesktopApplication”,”Configuration Item”, “UserName”);