Jan 3 2012

Year In Review 2011

It is that time of year where we reflect on the accomplishments of the passing year and look forward to the one to come. Here is a window into the past year in technology through this year’s popular posts on TechKnow Juixe.

Code and Design

Reviews and Rants

Retweet 2011

Year in Review


Sep 28 2011

Copy Records From One Database To Another

Most applications require a database of some sorts, and when working with a database you most likely will have a production, development, and test database to work with in addition to backups. A common problem I found is that sometimes I want to move rows of data for a particular database table between the different databases I work with. For example, I found I need to copy a subset of data from a table from the production database to the test database so that I had good data to test with. A typical approach is to create a backup from the production database and restore it as a test database, but if you just want to copy a subset of rows from a particular table that approach is overkill.

In SQL Server, you can use a insert statement to insert new rows in a database where you select and filter the data from a different database. This is also true for other database vendors but the SQL might be slightly different.

To further illustrate this, imagine I have a database table blog_posts in a production database called BlogProduction and a test database is called BlogTest and they both reside on the same SQL Server instance. From SQL Server Management Studio or SQLCMD or from where ever you run your SQL statement you can run the following statement.

INSERT INTO blog_posts
SELECT * FROM BlogProduction.dbo.blog_posts

If you run the above statement from the perspective of the BlogTest database, you are will insert all the records from the blog_posts table in the BlogProduction database into the blog_posts table in BlogTest. The above example, assumes the database owner is dbo. It is not required to name the database owner and the following does the same.

INSERT INTO blog_posts
SELECT * FROM BlogProduction..blog_posts

You can also add a where clause to the select statement to filter out certain data to insert to your current database.

INSERT INTO blog_posts
SELECT * FROM BlogProduction..blog_posts
WHERE id > 100

In this way, you can move data from different database tables from different databases.


Feb 1 2011

Copy Data Between Two Database Tables

There are many situations when you need to copy data from one database table to another one. You may want to migrate data from one database table to a new table, sometimes you need to do so to copy data to a backup table, other times to bulk load data from from different databases. To copy date between tables in a SQL Server database you can execute the following.

INSERT INTO Note (Id, Name)
SELECT Id, Name FROM Notes
WHERE name IS NOT NULL

The above state will copy date from a database table Notes to a new table Note where some condition is meet.

A typical database server instance hosts multiple databases, such as production and several test databases. You can copy data not only between tables within a database, but between databases within a database server instance. To copy between databases you need to use the fully qualified database name. For example, the following SQL snippet copies data from a tables called Notes in a database called TestDB.

INSERT INTO Note (Id, Name)
SELECT Id, Name FROM TestDB.dbo.Notes
WHERE name IS NOT NULL

Mar 30 2010

Top 25 Most Dangerous Programming Errors

I’ve always been interested in understanding common programming errors so that I can easily recognize and diagnose problems, hopefully without spending hours staring at my breakpoints in my debugger. Previously, I’ve written on Common Groovy Errors and Top Worse Java Errors.

The US Department of Homeland Security, under the Common Weakness Enumeration initiative put out the 2010 CWE/SANS Top 25 Most Dangerous Programming Errors. Most of the errors noted related to web application security programming errors.

  • Failure to Preserve Web Page Structure (‘Cross-site Scripting’)
  • Improper Sanitization of Special Elements used in an SQL Command (‘SQL Injection’)
  • Buffer Copy without Checking Size of Input (‘Classic Buffer Overflow’)
  • Cross-Site Request Forgery (CSRF)
  • Improper Access Control (Authorization)
  • Reliance on Untrusted Inputs in a Security Decision
  • Improper Limitation of a Pathname to a Restricted Directory (‘Path Traversal’)
  • Unrestricted Upload of File with Dangerous Type
  • Improper Sanitization of Special Elements used in an OS Command (‘OS Command Injection’)
  • Missing Encryption of Sensitive Data
  • Use of Hard-coded Credentials
  • Buffer Access with Incorrect Length Value
  • Improper Control of Filename for Include/Require Statement in PHP Program (‘PHP File Inclusion’)
  • Improper Validation of Array Index
  • Improper Check for Unusual or Exceptional Conditions
  • Information Exposure Through an Error Message
  • Integer Overflow or Wraparound
  • Incorrect Calculation of Buffer Size
  • Missing Authentication for Critical Function
  • Download of Code Without Integrity Check
  • Incorrect Permission Assignment for Critical Resource
  • Allocation of Resources Without Limits or Throttling
  • URL Redirection to Untrusted Site (‘Open Redirect’)
  • Use of a Broken or Risky Cryptographic Algorithm
  • Race Condition

Apr 20 2006

Show SQL Tables

When working with a database sometimes you will want to look up all the available database tables. To show the available tables in SQL Server you can do the following:

select * from information_schema.tables;

In Oracle you can use any of the following statements:

select * from user_tables;
select * from all_tables;

In MySQL I have been using the following since MySQL 3:

show tables;

But in MySQL 5 you can use the following:

select * from information_schema.tables;