Re: Broken sql string?

[Mark A. Taff]

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>My apologies
for not sending this to the list originally. <span style="mso-spacerun:
yes">�Allow me to elaborate.�
I am working on a `View Designer` similar to the interface that SQL
Server uses to create new views.� There
are a great many unknowns right now, and I need to get it working enough to
find out what works and what won&#8217;t (the relationship diagram for example, is a
big mystery right now).� So, to
speed up this process, I am only writing the code that I have to; if I can
recycle code from an existing section, I am.<span style="mso-spacerun:
yes">� So one piece of code I grabbed was the Display sub from
frmSQLOutput.

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>�

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>The code
in the sub that parses the query to find the FROM clause of the statement the
user entered in the HBX is looking for &#8220; FROM &#8220; (with leading and trailing
space). �So if there is a CRLF
immediately before the `F` instead of a space, the routine fails to find the
`FROM` clause, and hence fails to find anything else, like the table involved.

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>�

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>***CODE***

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'><span
style="mso-spacerun: yes">� 'Find the FROM clause. If it is inside
single quotes then we

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'><span
style="mso-spacerun: yes">� 'should try again - it won't in doubles
as there are no spaces

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'><span
style="mso-spacerun: yes">� 'in doubles anymore.

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'><span
style="mso-spacerun: yes">� iStart = 0

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'><span
style="mso-spacerun: yes">� bFlag = False

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'><span
style="mso-spacerun: yes">� bInQuotes = False

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'><span
style="mso-spacerun: yes">� While bFlag = False

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'><span
style="mso-spacerun: yes">��� iStart = InStr(iStart + 1,
UCase(szQuery), " FROM ")

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'><span
style="mso-spacerun: yes">��� If iStart = 0 Then 'No
FROMs found

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'><span
style="mso-spacerun: yes">����� bFlag = True

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'><span
style="mso-spacerun: yes">��� Else 'Found a FROM, check
it's not in quotes

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'><span
style="mso-spacerun: yes">����� For X = 1 To
iStart

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'><span
style="mso-spacerun: yes">������� If
Mid(szQuery, X, 1) = "'" Then bInQuotes = Not bInQuotes

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'><span
style="mso-spacerun: yes">����� Next X

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'><span
style="mso-spacerun: yes">����� If Not
bInQuotes Then bFlag = True

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'><span
style="mso-spacerun: yes">��� End If

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'><span
style="mso-spacerun: yes">� Wend

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>***END***

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>�

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>The
problem isn&#8217;t in the HBX itself, as far as I can tell, but rather in how we
parse the SQL string in the process of executing it.<span style="mso-spacerun:
yes">� The HBX is coloring properly.

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>�

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>The default
text in the HBX is set to <font
size=2 color=black face=Arial><span style='font-size:10.0pt;mso-bidi-font-size:
12.0pt;font-family:Arial'>"SELECT *" & vbCRLF & "FROM&#8221; in
the Form_Load event of my form.� As
we are dealing with queries here and not arbitrary SQL, this seems an
appropriate default value.� The
only instances I can think of when you wouldn&#8217;t need a FROM clause is when you
are selecting a function or a static value. <span style="mso-spacerun:
yes">�This is where the CRLF is coming from.

<font size=2 color=black
face=Arial><span style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:
Arial'>�

<font size=2 color=black
face=Arial><span style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:
Arial'>If changing the user&#8217;s input is not an option, then it seems to me maybe
we ought to rewrite the code to find the `FROM` clause even if there isn&#8217;t a
space before it.� What do you
think?

<font size=2 color=black
face=Arial><span style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:
Arial'>�

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>Regards,

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>�

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>Mark

<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>�

<font size=2 color=black
face=Tahoma>-----Original
Message-----
From: Dave Page
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 19, 2002
4:38 AM
To: 'Mark A. Taff'
Cc:
'[EMAIL PROTECTED]'
Subject: RE: Broken sql string?

<span
style='font-size:12.0pt'>�

<font size=3 color=black
face="Times New Roman">�<font
color=black>



<p class=MsoNormal style='mso-margin-top-alt:auto;margin-bottom:12.0pt;
margin-left:39.75pt;border:none;mso-border-left-alt:solid blue 1.5pt;
padding:0in;mso-padding-alt:0in 0in 0in 4.0pt'><font size=2 color=black
face=Tahoma>-----Original
Message-----
From: Mark A. Taff
[mailto:[EMAIL PROTECTED]] 
Sent: 19 February 2002 11:26
To: Dave Page
Subject: Broken sql string?<font
color=black>

<p class=MsoNormal style='margin-left:39.75pt;border:none;mso-border-left-alt:
solid blue 1.5pt;padding:0in;mso-padding-alt:0in 0in 0in 4.0pt'><span
class=EmailStyle16><span style='font-size:
10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>Dave,

<p class=MsoNormal style='margin-left:39.75pt;border:none;mso-border-left-alt:
solid blue 1.5pt;padding:0in;mso-padding-alt:0in 0in 0in 4.0pt'><span
class=EmailStyle16><span style='font-size:
10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'><span style='mso-tab-count:
1'>���������� Query
sql strings taken from my new sql hbx cause the display sub (from the sql
output form, now cannibalized) to break if the string contains vbCRLF.<span
style="mso-spacerun: yes">� The string in question might be
"SELECT *" & vbCRLF & "FROM addresses"

<p class=MsoNormal style='margin-left:39.75pt;border:none;mso-border-left-alt:
solid blue 1.5pt;padding:0in;mso-padding-alt:0in 0in 0in 4.0pt'><span
class=EmailStyle16><span style='font-size:
10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>�

<p class=MsoNormal style='margin-left:39.75pt;border:none;mso-border-left-alt:
solid blue 1.5pt;padding:0in;mso-padding-alt:0in 0in 0in 4.0pt'><span
class=EmailStyle16><span style='font-size:
10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>The code searches for
" FROM " to locate the from clause.<span style="mso-spacerun:
yes">� Should it be rewritten to allow for CRLF before any of the
tokens, or do we need to first parse the query string for CRLF and replace them
with single spaces?



<font
size=2 color=black face=Arial><span style='font-size:10.0pt;mso-bidi-font-size:
12.0pt;font-family:Arial'>�<font size=2 color=black
face=Arial><span style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:
Arial;color:black'>
<span
style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial;color:blue'>Hi
Mark,<span style='color:black;
mso-color-alt:windowtext'>

<font size=3 color=black
face="Times New Roman">�<font
color=black>

<font
size=2 color=blue face=Arial><span style='font-size:10.0pt;mso-bidi-font-size:
12.0pt;font-family:Arial;color:blue'>Please post this sort of message to the
pgadmin-hackers list so everyone knows what's going on :)<font
color=black>

<font size=3 color=black
face="Times New Roman">�<font
color=black>

<font
size=2 color=blue face=Arial><span style='font-size:10.0pt;mso-bidi-font-size:
12.0pt;font-family:Arial;color:blue'>I guess you're talking about the behaviour
of the HBX which won't colour the FROM if it follows a crlf (sorry, the talk of
cannibalized frmSQLOutput threw me a bit)? If so, then yes, this is a bug. The
problem with the HBX though, is that *many* hours of work and testing have been
put into it to make it work as well as it does. We did have code that coloured
better, however it's very slow. This was the major problem - if you pasted in 20K
of SQL, then you had to wait quite some time for it to colour.<font
color=black>

<font size=3 color=black
face="Times New Roman">�<font
color=black>

<font
size=2 color=blue face=Arial><span style='font-size:10.0pt;mso-bidi-font-size:
12.0pt;font-family:Arial;color:blue'>In answer to you question though, no, we
should not modify the user's input in any way. We used to replace crlf with
spaces before sending queries to the server, which was all fine and dandy until
someone complained that the PL/Perl function they wrote wouldn't work properly
- because it had lost all it's crlfs!!! If you can fix the HBX, or write a
better version though, that would be great.<font
color=black>

<font size=3 color=black
face="Times New Roman">�<font
color=black>

<font
size=2 color=blue face=Arial><span style='font-size:10.0pt;mso-bidi-font-size:
12.0pt;font-family:Arial;color:blue'>Regards, Dave.<font
color=black>