for ($x=0; $x<strlen($field);$x++) {
echo "(" .ord($field{$x}) . ") ";
}to see the ascii values to make sure nothing funky was there and it still stopped after the @@ (169)(169)
-Dave
At 12:50 AM 1/15/2004, David OBrien wrote:
Let's say i have a mysqldump type export script I wrote that someone screwed up (me) and I quoted every field even the
numeric ones. In this dump I have rows upon rows of this kind of data
INSERT INTO chd VALUES ( '80607fe5-00eb-f5c9-4d38-00ac10050a00' , '014d7fcd-00eb-f5c9-4d38-00ac10050a00' ,
'Kenderick' , '1991-02-10' , 'Male' , '2002-02-28' , '576' , '' , '' , 'Full-Time' , 'Summer Only' , 'İAfter Schoolİ' ,
'İChild Care Centerİİ(CCC) Summer Campİ' , 'İNon-Smokingİ' , 'İEnglishİ' , '' , ''
, '' , '' , '' , '' , 'İCMS - Tuckaseegeeİ' , 'İTransportation Providedİ' , '' );
The java programmer used @@ as internal field seperators (can't be changed)
I am reading this dump a line at a time stripping the data into an array and then writing back out to a new file
with the proper fields quoted or not quoted depending on the table and the db schema
"ccncc" would be "char char num char char" in the switch statement to tell the loop which ones to quote
or which ones not to.
ACTUAL SCRIPT AT THE END (kind of large)
In the example row above You see the @@(CCC) value?
Whenever It gets to a line with this combination it stops processing the rest of that line and terminates the field at the @@
Dump of the below program for the above row ( I put the *** before and after each field to see where it was dieing)
INSERT INTO chd VALUES ( ***80607fe5-00eb-f5c9-4d38-00ac10050a00*** ***014d7fcd-00eb-f5c9-4d38-00ac10050a00*** ***Kenderick*** ***1991-02-10*** ***Male*** ***2002-02-28*** ****** ****** ***Full-Time*** ***Summer Only*** ***İAfter Schoolİ*** ***İChild Care Centerİİ***
Then it just skips to the next record when it hits the (CCC). I could just use a text editor to replace all the @@( to
something and then write them back out at the end of the script but it's really bugging me and I thought a fresh pair
of eyes looking over it would help.
I know I have trims out the wazoo but I just rushed to post this before it got real late.
Anyone know of a way I can replace the (CCC) or the @@ using str_replace I've tried \'ing it, not slashing just about
every combination
-Dave
SCRIPT
[EMAIL PROTECTED] bin]# cat escape_sql.php #!/bin/php <? if ($argc != 2 ) { ?> Usage: <?php echo $argv[0]; ?> filename <? exit; } else { $handle = fopen( "$argv[1]" , "r"); while (!feof($handle)) { $count ++; $start = 0; $buffer = chop(fgets($handle, 1000000)); list($sql,$rest) = explode("(",$buffer); if (substr($sql , 0 , 2) == "--") { echo $sql ."\n"; } else { echo "$sql ( "; $rest = str_replace(");","" , $rest); $rest = str_replace("\(CCC\)","ZZZ",$rest);
$fields = explode("' , '" , $rest);
foreach ($fields as $field) {
$field = trim($field, "' ");
$field = trim($field, "\' ");
$table = str_replace("INSERT INTO ","", $sql );
$table = str_replace("VALUES" , "" , $table);
$table = trim($table);
switch ($table) {
case 'agc':
$schema = "cccccccccccccccccccc";
break;
case 'chd':
$schema = "ccccccnccccccccccccccccc";
break;
case 'cli':
$schema = "cncccccccccnncnncncncccc";
break;
case 'cliactionlog':
$schema = "cccccc";
break;
case 'cliaddress':
$schema = "ccccccccnn";
break;
case 'clicensus':
$schema = "ccccccccc";
break;
case 'clifollow':
$schema = "ccccnnnnnncccccccccccccccc";
break;
case 'cligeneral':
$schema = "ccccccccccccccccnccc";
break;
case 'cliref':
$schema = "ccccc";
break;
case 'clistats':
$schema = "cccccccncccccc";
break;
case 'cnd':
$schema = "cccc";
break;
case 'com':
$schema = "cncccccccccccccncnncnnccccccccc";
break;
case 'comactionlog':
$schema = "cccccc";
break;
case 'comagcinfo':
$schema = "ccccccccnccc";
break;
case 'dfl':
$schema = "ccc";
break;
case 'dys':
$schema = "cccccc";
break;
case 'ent':
$schema = "cnnccc";
break;
case 'fld':
$schema = "ccccccnncnn";
break;
case 'inm':
$schema = "cccccccccccccccccccccccccccccc";
break;
case 'obj':
$schema = "ccn";
break;
case 'pdr':
$schema = "ccnccccccnncnncnnccccccccc";
break;
case 'pdractionlog':
$schema = "cccccc";
break;
case 'pdraddress':
$schema = "cccccccccnn";
break;
case 'pdrattributes':
$schema = "cccccccccccccccccccccccccccccc";
break;
case 'pdrgeneral':
$schema = "ccccccccccccccnnncnncccccnccccccc";
break;
case 'pdrspecificsccc':
$schema = "ccccccnnnncnnncnnnnnnncnnncncnccc";
break;
case 'pdrspecificsfcc':
$schema = "ccccccccccccccc";
break;
case 'pop':
$schema = "ccnnnnnncnnn";
break;
case 'qry':
$schema = "ccccccc";
break;
case 'rts':
$schema = "ccnnnnnnnnnnn";
break;
case 'sft':
$schema = "cccccccccccccccccccc";
break;
case 'stf':
$schema = "cccnnc";
break;
case 'typ':
$schema = "cncccccncc";
break;
case 'usr':
$schema = "ccccncccc";
break;
}
if ($schema{$start} == "n") {
if ($field != "") {
$field = trim($field, "' ");
$field = trim($field, "' ");
} else {
$field = trim($field, "' ");
$field = trim($field, "' ");
$field="NULL";
}
if ($start == 0) {
# echo " " . $field ." ";
} else {
# echo "," . $field ." ";
}
$start ++;
} else {
echo "\n***".$field."***";
if ($field != "") {
$field = trim($field, "' ");
$field = trim($field, "' ");
} else {
$field = trim($field, "' ");
$field = trim($field, "' ");
$field = "";
}
if ($start == 0) {
# echo "'" . mysql_escape_string( $field ) ."'";
} else {
# echo ",'" . mysql_escape_string( $field ) ."'";
}
$start ++;
}
}
# echo ");\n";
}
}
fclose($handle);
}
?>
-- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
-- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
