Database

SQL Injections {1}

by Claudia J
The article that I picked this week was a peer reviewed called “Preventing SQL injection attacks in stored procedures.” The author talks about that an SQL injection attack mainly targets interactive web applications that use database services which accept user inputs and use them to form SQL statements at runtime. It also says that an SQL injection attack the attacker usually provides malicious SQL queries segments that cause the database to result in different requests.
From what I understood from the author is that it results hard to avoid this type of attacks. It mentions that one way is to examining dynamic SQL query semantics at runtime but is not 100% secure that will not get SQL injection attacks. The author proposes a novel technique to defend against the attacks targeted at stored procedures where application analysis with runtime validation will help to eliminate the occurrence of such attacks.
The authors go in depth on other types of queries we can use to help prevent SQL injections. SQL injection is a very common technique hackers use to attack underlying databases by altering the programs behavior. This article is good because it connects to the class content specially right now that we are in the middle of a project and working with SQL queries. It gives us an insight of the type of vulnerabilities that are out there when programming. It is important to try to make sensitive files of our databases “hard” to find to make it harder for hackers to get into our system.
Wei, K, Muthuprasanna, Suraij. (2006) Preventing SQL injection attacks in stored procedures. MIS Quarterly, PP 1-8. Retrieved November 11, 2012. http://0-ieeexplore.ieee.org.opac.library.csupomona.edu/stamp/stamp.jsp?tp=&arnumber=1615052&tag=1 read more...

How to Reduce Damage from SQL Injection {1}

by Tseng H. K.
The article I read this week is “How to Prevent a SQL Injection Attack” by Tsveti Markova. Since many of us mentioned SQL injection on their post this and last week, I decided to look for ways to prevent/reduce damages from SQL injection attack. I choose this article because most of other articles are coding basis it was hard to understand. Since we just started to learn SQL quries, I wanted to look for more basic prevention principles that apply any programming language without knowing high level coding skills. read more...

Removing Vulnerabilities in SQL Code {3}

by Leonardo S
The article I chose for this week is titled “Using Automated Fix Generation to Secure SQL Statements”.  The main purpose of the article is to propose a certain method of securing code from SQL injection attacks.  The article starts by stating that since 2002, more than half of all cyber attacks were done by exploiting input validation vulnerabilities.  SQL injection attacks accounted for 20% of all of those attacks.  For those who don’t know, an SQL injection attack is when a hacker inputs his or her own SQL statement in order to gain access to certain information.  The author’s proposed method of helping to prevent this kind of attack to java code is by using prepared statements rather than just plain text SQL statements.  His reasoning for this is that prepared statements limit the input that could affect the statement.  The author tested this by using prepared statements in five mock java programs which contained vulnerabilities.  Their conclusion was that the vulnerabilities were removed in each program. read more...

Auditing SQL {3}

by Rudy P
This week I will be blogging about the journal entry “Auditing a Batch of SQL Queries” by Rajeev Motwani, Shubha U. Nabar, and Dilys Thomas of Stanford University. This Journal entry talked about ways SQL Queries are audited and how to determine suspicious SQL queries. The Journal makes mention of a command AUDIT, which I had never seen before. They use it an example: read more...

SQL Server Service Broker {3}

by Ming X
The article I read is called an Introduction to SQL Server Service Broker, by Deanna Dicken. As she mentions, “Service Broker is a technology built into SQL Server and utilized by the engine for its internal asynchronous processing.” SSB is a new architecture introduced with SQL Server 2005 and enhanced further in SQL Server 2008 and later versions that allows you to write asynchronous, decoupled, distributed, persistent, reliable, scalable and secure queuing/message based applications within the database itself.
There are some benefits of SSB including:
Reliability
Guaranteed Delivery
Replay-ability
Separation of concerns
Scalability
Priority processing
Guaranteed ordering
A SSB integration consists of Conversations or Conversation Groups, Message Types, Contracts, Services and Queues. Users can use T-SQL language to implement these pieces. read more...

DbNinja the New Wave of MySQL {2}

by Andrew S
The author of the article talks about SQL and the myriad of different programs that people can use to tinker with SQL databases.  One of the SQL tools the author then references is DbNinja, which is like no other MySQL clients.  What makes it unique from other applications is that you are able to define multiple hosts while examining or modifying any of its databases.  The tool gives detailed information such as the server status, processes, and system variables. DbNinja gives off the feel of a desktop application and is more user-friendly that its SQL application counterparts.  Probably one of its most appealing qualities is that it is free for personal use, so anybody is able to download it and use it. read more...

New Oracle MySQL Migration Tool {1}

by Allen D
Oracle has offered a new tool that allows the users to migrate data from the SQL Server it’s a MySQL database. The tool allows for applications written on the server to be easily tweaked into the database. Additionally, Oracle has offered a new plug-in tool that gives users with no experience on MySQL to work with MySQL data inside Microsoft Excel. With these new tools, Oracle announced that companies can save up to 90% less total costs of ownership by using the new MySQL tools compared to using the traditional SQL Server. Oracle’s efforts to implement migration programs had been a fixed tradition in the software industry for quite some time. Competitors such as EnterpriseDB had also been producing compatible software to Oracle’s SQL flagship database. Companies such as Oracle and EnterpriseDB are very enthusiastic and eager to increase their database market share into corporate IT. Their strategies are not only focused on generating software license revenues and subscription contracts but also to create new environments that enable them to sell compatible applications and other tools. read more...

Design and Performance of Databases {Comments Off on Design and Performance of Databases}

by Eric C
When it comes to building databases, performance is perhaps the top priority for database engineers. Speed and efficiency is the key when it comes to gathering information using queries for end users. There are various ways to improve the efficiency of databases and that includes the incorporation of vertical and horizontal partitioning. In a peer reviewed article written by Agrawal, Narasayya, and Yang, entitled “Integrating Vertical and Horizontal Partitioning into Automated Physical Database Design,” the authors discuss how vertical and horizontal partitioning can both improve the performance and manageability of databases. Horizontal partitioning takes the rows of tables that have common values and puts them together into one or more tables. Vertical partitioning is the same concept, except using the columns of several tables. Furthermore, there are two main methods of partitioning, as mentioned in the article: hash and range. Hash involves the distribution of rows evenly across different tables with the use of a hash key. Range involves the partition of minimum and maximum values of data in many columns. In order to make the database easier to manage with these methods, it is required to have the indexes and tables aligned. An index is aligned when the index uses the same partitioning technique as the tables. However, achieving such tasks is complex and therefore the physical design is very important. For example, there are many ways to horizontally and vertically partition a database, as well as the alignment and as a result, choosing the correct design is critical. If such designs are not implemented correctly, that database could perform slower or even cause lockups when running queries. Once correctly implemented, the database can receive an improvement of up to 20% in running queries. read more...

Optimizing SQL Server Performance {1}

by Kathy S
In this journal article the authors state that many times efficiency and performance are the last criteria considered when designing and developing new applications using a database. Sometimes the application does not display the information requested to the database in a reasonable time or completely fails to display it. The reasons may be related to the application design, but in many cases the DBMS does not return the data quickly enough, due to the non-use of indexes, deficient design of the queries and/or database schema, excessive fragmentation, use of inaccurate statistics, failure to reuse the execution plans or improper use of cursors. The authors then review the objectives that should be considered in order to improve performance of SQL server instances. The most important objectives are: 1) Designing an efficient data schema, 2) Optimizing indexes, stored procedures and transactions, 3) Analyzing execution plans and avoiding recompiling them, 4) Monitoring access to data, 5) Optimizing queries. The authors conclude that optimization is an iterative process and includes identifying bottlenecks, solving them, measuring the impact of changes and reassessing the system from the first step as to determine if satisfactory performance is achieved. They also highlight the fact that a superior performance can be obtained by writing an efficient code at the application level and properly using the design and database development techniques. read more...

Benefits of Range-Based Partitioning {1}

by Shigom H
Database Partitioning is breaking up large amounts of data into smaller parts which can be beneficial if done correctly. The purpose behind database partitioning is to simplify managing large amounts of data and boost performance. Performance is boosted because segments of data can be stored in different directories and because MySQL will only search the segments that contain the data the query is asking for. The picture below illustrates an example of application requests being handled by limited resources which can slow down the system. read more...