SQL

SQL Injection {Comments Off on SQL Injection}

By Winston L.

Database is a brilliant invention in this age of technology. From the Database page on Wikiperdia, database has existed for 45 years since when first proposed by Edgar Codd in 1970, however, not until the computer hardware’s capability became strong enough to handle large data processing were the database concept and database management systems (DBMS) widely implemented. After the birth of Internet, the demand for a decent application to manage large database increased even higher, and it is partly because people have found that database can be used to utilize everything, from business to scientific research. Nowadays, there are many major DBMS, such as IBM DB2, MS SQL, MySQL, and Oracle. All of them are based on the standardized SQL language, and that makes them vulnerable to one simple but very dangerous attack on the database, the SQL Injection. In this blog, SQL Injection attack technique, its impact, its victims, and prevention solutions will be discussed.

read more...

DeepSQL: The Next Generation of Database Optimization {Comments Off on DeepSQL: The Next Generation of Database Optimization}

By Andy C.

Due to the explosive growth of Big Data in this world, SQL, the programming language for interacting and modifying databases, was also required to evolve in order to keep up. With such large amounts of data, servers needed to be expanded in order to accommodate all of the data. However, more servers would also mean more time to process data. A platform that can hold and process all of the data efficiently was needed. With databases being increasingly larger and increasingly more necessary for operations within a company, many companies needed to look for alternative solutions to handle the database for higher efficiency. Many different platforms to store and process data with different versions of SQL were developed, and a recent development in 2015 was the invention of DeepSQL by Deep Information Sciences. DeepSQL is a storage engine that uses MySQL, which transforms a MySQL database into a database that continually tunes itself for maximum optimization.

read more...

PostgreSQL is a way! {2}

What is PostgreSQL?

Companies are always looking for database system to take care of complex data infrastructure in order to solve specific business needs, lower the cost of operations, and reduce the deployment time. Some case studies show PostgreQL is one of useful tools. PosgresQL is an open source object-relational database system (DBMS). In term of source relational database system, it is a relation database with an object-oriented database model which supports objects, classes and inheritance. PostgresQL runs major operating systems such as Linux, UNIX(Mac OSX, Solaris as well as Windows. It also supports for foreign keys, joins, triggers and stored procedures with multiple languages.

read more...

2014, New Year For SQL Server {1}

The next step in Microsoft’s Relational Database Management System looks promising for efficiency and speed in transaction processing. Microsoft recently held a community technology preview for the massive project and this is what was found. SQL Server 2014 will come with their new In-Memory Online Transaction Processing (OLTP) feature called Hekaton that is a built in part of the database system. Hekaton works by selecting data that is being read or written more frequently and moves it into the server’s working memory. It allows for the priority data to be quickly accessed and ready for transactions or updates on the fly. By enabling this Hekaton optimization, it detects which data should be used by the working memory and optimizes the specified data into main memory. Integrity of the data is maintained by writing any transactions to a separate log file in case of system outages. Beyond just the increased speed, companies can expect to see a saving in cost as Hekaton would reduce the computational requirements necessary to get data processing done, which would require less servers and hardware.

read more...

Compromised Data Quality by Malware {Comments Off on Compromised Data Quality by Malware}

In an article from PCWorld entitled “Symantec warns of malware targeting SQL databases,” there has been a spread of malware infecting SQL databases around the world. Although not a serious threat, it could pose to destroy data quality within the database. Originally targeted to Iran, the malware called W32.Narilam, looks for Microsoft SQL databases on the infected server. If Microsoft SQL is found, the malware then finds specific keywords from a file, such as account and financial bond, and then replaces those keywords with random characters. Database administrators who do not make frequent backups of the database will have corrupted data and the loss of data integrity, which could prove disastrous for customers’ data, especially in a banking database.

read more...

SQL, Easier in the Future {Comments Off on SQL, Easier in the Future}

Depending on the task, writing SQL queries can get complicated, in the article “Interactive SQL Query Suggestion: Making Databases User-Friendly”  Ju Fan proposes a neat tool for making SQL easier. By simply inserting keywords the tool will generate the corresponding SQL statements. This is similar to Microsoft excel, those who are unfamiliar with excel can type in keywords and excel will find the corresponding formula. SQLSUGG  is a program that suggest queries while the user types. Many databases already offer this type of  functionality but SQLSUGG differentiates itself by its ability to suggest advance queries. Unfortunately most database systems that offer this keyword functionality generate SQL queries from simple keywords that are only beneficial to “casual users”  but deemed useless to database administrators and SQL programmers.

read more...

MySQL: A threat to bigwigs? {1}

David Kirkpatrick, of Fortune.com and contributor to CNN Technology, writes in his article, “MySQL: A threat to bigwigs?” that MySQL is the rising Linux of backend systems. He begins by explaining the structure of MySQL (which is based on the SQL language) and the structure of general open source software. Because MySQL is open source, it has a lot more potential to grow when placed next to the pace and speed that commercial software is growing because of the huge “fan base” of developers that open source attracts. The catch: not only are these “fan” developers users of MySql, but they are required, by MySQL’s ethical policy, to share any changes they may have made to the code to the MySQL database – which is in the end a win-win situation for both developer, end user, and MySQL as a public service. As Kirkpatrick points out in a citation of a quote made by MySQL CEO Scott McNealy, huge companies like Yahoo and Google depend on MySQL to get their work done – and if they can do it, so can smaller companies. Compared to a $395 per year for a server, compared to Oracle’s $20,000, MySQL is a no brainer. Kirkpatrick ends by stating that although there are still shortcomings in the free opensource software, MySQL has a huge future ahead of it, as confided in the confident words of it’s CEO, Scott McNealy in saying, “People ask me ‘What’s wrong-why are you leaving money on the table?’ We say ‘You should ask the other database companies what is wrong with their cost structure.”

read more...

A Look at SQL Queries {Comments Off on A Look at SQL Queries}

For this weeks blog post I read an academic journal by Mohammad Dadashzadeh called, “A Simpler Approach to Set Comparison Queries in SQL.” The article talks about set comparison queries which are queries that have results that can only be determined by comparing two sets of data. An example of a comparison query would be Which suppliers will be able to supply all the parts that they are currently shipping or which suppliers are shipping exactly the same parts as supplier SI? In contrast to these two queries a query such as “which suppliers are shipping at least one red part” can be obtained by matching or joining data from one table. The article explains how queries involving set comparison are very difficult to formulate in relational query languages because they must use the error prone EXISTS function. Their solution is to avoid the EXIST function and use the built in SET function as well as the COUNT function.

read more...

SQL Packages and Data Warehousing {1}

The article I read is called the development of ordered SQL packages to support data warehousing, by Wilfred Ng and Mark Levene. “Data warehousing is a corporate strategy that needs to integrate information from several sources of separately developed Database Management Systems.” The authors mention that future database management systems should provide adequate facilities to manage a wide range of information arising from such integration. Since the order of data is usually involved in business queries; users can extend the relational model to incorporate partial orderings into data domains and describe the ordered relational model. User can use OSQL, which allows querying over ordered relational databases. Ordered SQL (OSQL) is an extension of the Data Definition Language (DDL) and Data Manipulation Language (DML) of SQL for the ordered relational model. OSQL can be applied to solve various problems that arise in relational DBMSs involving applications of temporal information, incomplete information and fuzzy information under the unifying framework of the ordered relational model. There are three OSQL packages: OSQL_TIME, OSQL_INCOMP and OSQL_FUZZY.

read more...

Friendly SQL Queries {7}

The peer review article that I read was called “Interactive SQL query suggestion: Making databases user-friendly” by Ju Fan talks about how SQL is a good tool for querying relational databases. The author mentions that it is hard for beginners with a lack of programming skills to make efficient SQL queries as they require being proficient in SQL syntax and having a good understanding of the underlying schema. The author proposes a SQLSUGG which is a user-friendly keyword-based method to help people create SQL queries. This method suggests SQL queries as users type the keyboard and also helps to save what the users type to avoid them having to type everything multiple times which avoids SQL debugging in a way.

read more...