Oledb/.Net Data Access bug

• Phil.Nicholas

  SSCrazy

  Points: 2147

  More actions

  June 9, 2006 at 4:00 am

  #103177

  Found this interesting problem with some code one of our developers was using

  create table CharTest

  (

  firstname varchar(4) null,

  surname varchar(4) null

  )

  INSERT INTO CharTest (firstname) values ( char(1) + char(1) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(1) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(2) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(3) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(4) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(5) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(6) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(7) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(8) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(9) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(10) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(11) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(12) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(13) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(14) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(15) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(16) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(17) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(18) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(19) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(20) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(21) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(22) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(23) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(24) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(25) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(26) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(27) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(28) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(29) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(30) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(31) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(32) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(33) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(34) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(35) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(36) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(37) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(38) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(39) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(40) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(41) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(42) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(43) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(44) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(45) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(46) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(47) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(48) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(49) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(50) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(51) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(52) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(53) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(54) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(55) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(56) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(57) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(58) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(59) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(60) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(61) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(62) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(63) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(64) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(65) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(66) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(67) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(68) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(69) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(70) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(71) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(72) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(73) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(74) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(75) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(76) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(77) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(78) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(79) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(80) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(81) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(82) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(83) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(84) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(85) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(86) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(87) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(88) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(89) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(90) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(91) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(92) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(93) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(94) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(95) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(96) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(97) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(98) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(99) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(100) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(101) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(102) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(103) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(104) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(105) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(106) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(107) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(108) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(109) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(110) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(111) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(112) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(113) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(114) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(115) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(116) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(117) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(118) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(119) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(120) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(121) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(122) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(123) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(124) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(125) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(126) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(127) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(128) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(129) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(130) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(131) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(132) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(133) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(134) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(135) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(136) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(137) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(138) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(139) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(140) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(141) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(142) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(143) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(144) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(145) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(146) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(147) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(148) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(149) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(150) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(151) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(152) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(153) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(154) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(155) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(156) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(157) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(158) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(159) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(160) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(161) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(162) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(163) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(164) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(165) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(166) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(167) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(168) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(169) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(170) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(171) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(172) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(173) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(174) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(175) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(176) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(177) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(178) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(179) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(180) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(181) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(182) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(183) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(184) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(185) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(186) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(187) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(188) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(189) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(190) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(191) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(192) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(193) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(194) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(195) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(196) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(197) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(198) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(199) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(200) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(201) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(202) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(203) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(204) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(205) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(206) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(207) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(208) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(209) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(210) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(211) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(212) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(213) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(214) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(215) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(216) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(217) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(218) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(219) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(220) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(221) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(222) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(223) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(224) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(225) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(226) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(227) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(228) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(229) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(230) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(231) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(232) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(233) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(234) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(235) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(236) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(237) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(238) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(239) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(240) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(241) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(242) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(243) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(244) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(245) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(246) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(247) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(248) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(249) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(250) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(251) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(252) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(253) )

  INSERT INTO CharTest (firstname) values ( char(1) + char(254) )

  select * from chartest where surname is not null

  returns no records

  select firstname,surname from CharTest

  returns columns apparently with data in the surname column even though they are null

  the reason for this is that the oledb/.Net drivers are interpreting (char(1)) as the end of the forename columns data and the data after that as surname data. You can prove it by rejigging the column order:

  select surname,firstname from CharTest

  Presumably having additional columns such as integers will cause even more strange issues with the drivers. Very strange as the string terminator is char(0) which you'd assume is how it would be splitting the buffer.

  
  Phil Nicholas

• June 12, 2006 at 8:00 am

  This was removed by the editor as SPAM

• Vinay Kirtane

  SSC Enthusiast

  Points: 143

  More actions

  June 12, 2006 at 9:22 am

  #643146

  Not sure if you have any issue here but personally I won;t classify this as a bug. If you need to use special characters then any language will need escape characters to tell complier/interpreter to treat it differently.