SELECT Statements & inputs

[JaneB19]

Hi,

I'm hoping that somebody can help me with this one?

I've got the following line of code

strSQL = "SELECT DISTINCT n2.Abbreviation FROM Abb AS n1 INNER JOIN Abb AS n2 ON n1.Word = n2.Word WHERE ((n1.Word= '" & ClueBX & "') AND NOT (n1.Abbreviation = n2.Abbreviation) AND (LEN(n2.Abbreviation) = '" & No_Letts & "') AND (n2.Abbreviation Like ('" & pattern & "')));"


but for some reason, it will read in the ClueBX, and the No_Letts but NOT the pattern.

The ClueBX is set to the value of a textarea, and No_Letts is the selected value of a radiobutton list. pattern is meant to be the value of a textbox, where the user can search for a word like the pattern that they've entered.

Would there be any reason that coding wouldn't work? I know that the SQL query works within Access with the LIKE but it doesn't want to work within Visual Studio.net.

Thanks in advance
Jane

 

[jzelhart]

Can you give us a sample pattern that is entered. Wild cards work differently depending on the database backend - do you use Access or SQL Server as a backend?

Hope everyone is having a great day!

Thanks - Jennifer

 

[klaz2002]

Jane,
My first reaction to the code you put up is stop!!! You have let anyone with a small amount of sql experience hack into your routine. By inserting the single quote ' and then a SQL comment -- they can really do damage. What you should try to do is use the Stored Procedures to actually execute any kind of SQL (DB) call.
On the other hand your var may have some extra whitespace at the end. Sometimes this has stopped my pattern from working.
The way I get these painfull patterns to work is I actually execute them first in the SQL query tool.

If pattern is a text box could it be as simple as Pattern.Text for the text portion?
Klaz

Please let us know.

 

[jzelhart]

Klaz is right on the SQL hack. When possible, use SQL Parameters if you want to not use Stored Procedures.

What are you trying to do?

Hope everyone is having a great day!

Thanks - Jennifer

 

[JaneB19]

Thanks for your replies!

The web page that this coding is on, is not for a public site, so the hacking bit really doesn't matter!

I've tried pattern.text before but it didn't make any difference.

Also, I executed my code in Access and it worked in there, but it won't on my .net page?!

I'm beginning to dislike ASP.net! Previously when I submitted my form, I just got an error saying that the page could not be displayed, now I'm getting
Object reference not set to an instance of an object
relating to the pattern box. I've been through my coding and made sure that it is . . . !?

Does anybody know the sudden change in errors?

Thanks again
Jane

 

[jzelhart]

Can you include your HTML code so that I can try to recreate the problem.

Hope everyone is having a great day!

Thanks - Jennifer

 

[JaneB19]

Hi Jennifer,

I've managed to solve the object reference problem, but now I'm getting nothing returned.

It loads up the page but doesn't pull any solutions from the database, even though I know that there should be 2!

Would you still like some of my coding?

Jane

 

[crazyboybert]

The web page that this coding is on, is not for a public site, so the hacking bit really doesn't matter!

If your working on a corporate Intranet do not be too complacent. There have been plenty of news stories in rags such as ComputerWeekly (you probably don't get this in the States?) which suggest the majority of hacking attempts on internal systems come from inside the network. Security is something we developers always seem to be a little too complacent about.

Just to give you an idea of the sort of damage that can be done there are system stored procedures in SQL Server which can execute Shell commands that will reformat the C drive, if your connecting to the database using the 'sa' account, which again so many do, this is a real threat!

Additonally there are plenty of other benefits of making the step to using procedures other than security. Your query is compiled by SQL into a Execution Plan which give syou improved performance for one thing. Your code is more readable, maitainable and reusable for another.

Just my thoughts on the matter...

In terms of not getting your results have you tried running the query in something like Query Analyser to check it returns some results?

Code samples are always useful here for specific problems rather than theoretical ones as it allows us to spot where your going wrong.

Rob

Go placidly amidst the noise and haste, and remember what peace there may be in silence - Erhmann 1927

 

[JaneB19]

Thanks for the advise, but this is for my own use, so it won't be public as I said.

I'm in Scotland, so yeah I've got access Computer Weekley, must check that magazine out.

The following is the majority of my coding for what I'm attempting to do. It's a cryptic crossword solver if anybody's interested.

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
'Create the connection string to the database
Dim strConnection As String = "Provider = Microsoft.Jet.OleDb.4.0;Data Source = C:\Inetpub\

http://wwwroot\WebApplication1\Project\CrypticCrosswordSolver\Project.mdb;"

'Set the object connection as a database connection and
'the connection string to the strConnection
Dim ojbConnection As New OleDb.OleDbConnection(strConnection)

'Declare Clue_Type as String type and set it to the selected value
'of the ClueTypeList radioButtonList
Dim Clue_Type As String = ClueTypeList.SelectedItem.Value

'Declare No_letts as String type and set it to the selected value
'of the NoLettersList radioButtonList
Dim No_Letts As String = No_Letters.SelectedItem.Value

'Declare ClueBX as String type and set it to
'the text of the Clue_box textarea
Dim ClueBX As String = Clue_box.Text

'Declare the SQL string that will pull data from the database
Dim strSQL As String

'If the selected value of the Clue_Type
'radioButtonList is"Abbreviation" then pull
'the relevant fields from the Abbreviation tables
If Clue_Type = "Abbreviation" Then
strSQL = "SELECT DISTINCT n2.Abbreviation FROM Abb AS n1 INNER JOIN Abb AS n2 ON n1.Word = n2.Word WHERE ((n1.Word= '" & ClueBX & "') AND NOT (n1.Abbreviation = n2.Abbreviation) AND (LEN(n2.Abbreviation) = '" & No_Letts & "') AND (n2.Abbreviation Like ('" & PatternBox.Text & "')));"

'Else if the selected value of the Clue_Type
'radioButtonList is "Straight" then pull
'the relevant fields from the Straight table
ElseIf Clue_Type = "Straight" Then
strSQL = "SELECT Straight.Clue, StraightSolutions.Solution FROM StraightSolutions INNER JOIN Straight ON StraightSolutions.Pk = Straight.FK WHERE (((Straight.Clue)= '" & ClueBX & "') AND ((StraightSolutions.NoLetters)= '" & No_Letts & "')) OR (((StraightSolutions.Solution)= '" & ClueBX & "') AND ((StraightSolutions.NoLetters)= '" & No_Letts & "') AND (StraightSolution.Solution Like ('" & PatternBox.Text & "')));"

'Else if the selected value of the Clue_Type
'radioButtonList is "Word_Exchange" then pull
'the relevant fields from the Exchange table
ElseIf Clue_Type = "Word_Exchange" Then
strSQL = "SELECT n2.Exchange FROM WordExchange AS n1 INNER JOIN WordExchange AS n2 ON n1.Word=n2.Word WHERE n1.Word= '" & ClueBX & "' AND NOT n1.Exchange=n2.Exchange AND LEN(n2.Exchange)= '" & No_Letts & "' AND (n2.Exchange Like ('" & PatternBox.Text & "'));"

'Else if the selected value of the Clue_Type
'radioButtonList is "Number_Exchange" then pull
'the relevant fields from the Numbers table
ElseIf Clue_Type = "Number_Exchange" Then
strSQL = "SELECT n2.Exchange FROM NumberExchange AS n1 INNER JOIN NumberExchange AS n2 ON n1.Number=n2.Number WHERE n1.Exchange= '" & ClueBX & "' And Not n1.Exchange=n2.Exchange And LEN(n2.Exchange)= '" & No_Letts & "' AND (n2.Exchange Like ('" & PatternBox.Text & "'));"
End If

'Declare and set the object Command to the query, and connection
Dim ojbCommand As New OleDb.OleDbCommand(strSQL, ojbConnection)

'Open the ojbConnection
ojbConnection.Open()

'Attach the data gathered from the database to the DataGrid
'set it as the source for the datagrid
AdvancedSolutions1.DataSource = ojbCommand.ExecuteReader()

'Put the data in the datagrid (AdvancedSolutions1)
AdvancedSolutions1.DataBind()

'Close connection to database
ojbConnection.Close()

End Sub

I know that there is a lot of code there, but I wasn't too sure which part folks wanted.

Thanks again for your help!
Jane

 

[jzelhart]

You didn't tell us if the backend is SQL or Access.

Can you also give us a sample value for each of the SelectedItem.Values and Textboxes

Also try it without the Pattern - does it work? If so then it might be the wildcards that you are using to create your pattern. Like * versus % --- I have had to switch those a couple of times.


Hope everyone is having a great day!

Thanks - Jennifer

 

[jmeckley]

your like statement doesn't include wild cards. If you use LIKE you must use wild cards. If you don't it's a striaght match and uses the = comparison.

Try LIKE n2.Abbreviation Like ('%" & pattern & "%').

Jason Meckley
Database Analyst
WITF

 

[bassguy]

I just want to jump on the STOP bandwagon. SQL injection is really easy with how you are setting this up.

Even if your "Safe" on an Intranet site what happens when a user wants data with an apostrophe in it like "Dad's rootbeer"

What if your users are trying to quote something like I just did in the last sentence?

I learned all about SQL injection while I was working at another company. The programmers (I was only a Trainer)like you had assumed that the intranet was "Safe".

I looked at it like it was a nice lab for me to play with.

well anyway FWIW use procedures and Parameters

Bassguy