locked
problem with like query within vb.net site RRS feed

  • Question

  • User2146470223 posted

    Hi,

    On my site I have this SQL query:

    SELECT * FROM LUX WHERE wort COLLATE Latin1_General_CS_AS = '" & Replace(Trim(Tempit2), "'", "''") & "' OR wort COLLATE Latin1_General_CS_AS = '" & Tempit2b & "' OR wort like '" & str_suchlike & "%' COLLATE Latin1_General_CS_AS OR wort LIKE '" & str_suchlikeb & "%' COLLATE Latin1_General_CS_AS ORDER BY wort DESC

    which is populated automatically while escaping single quotes.

    For this example string:

    D'Versiounsnummer ass um Serveur desaktivéiert.

    this leads to this query:

    SELECT        id, wort, verb, artikel, adjektiv, adverb, prepo, nomen, pronomen, konjunk, interjekt, numerale, done, zusatz, name, verwurf, unsicher, abk, lang, partikel, erausnam,
                             erausnamv, erausnamofk, bearb, erausnamk, ertreman, ereton, eifelref, richtig, falref, slang, eifelref2
    FROM            LUX
    WHERE        (wort COLLATE Latin1_General_CS_AS = 'D''') OR
                             (wort COLLATE Latin1_General_CS_AS = 'd''') OR
                             (wort LIKE 'D''Versiounsnummer%' COLLATE Latin1_General_CS_AS) OR
                             (wort LIKE 'd''Versiounsnummer%' COLLATE Latin1_General_CS_AS)
    ORDER BY wort DESC

    which works just fine in SQL server interface. But on the site itself it throws the following error:

    System.Data.SqlClient.SqlException: Kein schließendes Anführungszeichen nach der Zeichenfolge 'D''.
    Falsche Syntax in der Nähe von 'D''.

    -> no closing single quote after string 'D''. Wrong syntax next to 'D''

    Am I missing something?

    Thanks for the help,

    Pascal

    Thursday, January 21, 2016 11:17 AM

Answers

  • User2146470223 posted

    sorry for the late reply but did not get to programming for quite some time …

    @Weibo

    I know about SQL injection but where I'm programming right now there will be no sql injection and if anyone would get access there, he would not need injection anymore anyway.

    at the moment I only want to get my app running the way it is supposed to. As it is quite complex and I don't know yet how exactly to get where I need to get (the goal is very clear and I just have to find the way there) so I have to change my code from scratch quite often as I at some point realize that I could use better methods or solutions than the ones I have. So using parameters or not is the least of my worries. btw: I had everything coded with parameters using stored procedures and it generated more issues at one point so I decided to drop these and use normal queries until I get close to the wished result, when I would need to publish everything and would only need to make everything as safe as possible.

    in the meantime I switched to SQL Server 2016 and after some time back to using parameters. Since the switch to 2016 the issue was not repro anymore. So we could close this thread but I don't know which post to set mark as solution as it was not really solved.

    @Patrice

    well the code I have generates exactly that query and that's why I don't understand why it is not working. using the very same query in server managemen studio (copying it there) it works perfectly but not from the app and I don't know why because all quotes are correctly quoted out.

    The escaping is done automatically by the app and up to now it never failed and as it was not changed for quite some months I don't know why it should stop working and not escape the way it always did.

    cheers,

    Pascal

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 19, 2016 10:51 PM

All replies

  • User77042963 posted

    Please change your query to use parameters to pass values.  String concatenation will create security issues other than the coding issue you have now.

    To answer your question, inT-SQL, you need to double single quote to escape the single quote. But by using parameterized query, this will be handled internally.

    Thursday, January 21, 2016 3:06 PM
  • User2146470223 posted

    Hi Limno,

    that does not answer my question as it already states there that the single quotes are escaped (double) before they are added to the query. You can also see this in the example I provided.

    I used stored procedures with parameters before but that 'solution' lead to even more problems as soon as Like operator was used.

    Thursday, January 21, 2016 3:13 PM
  • User77042963 posted

    Using parameters will solve your problem not creating problems.

    Can you try this one:

    WHERE wort = 'D'''  OR wort = 'd'''  OR  wort LIKE 'D''Versiounsnummer%' OR  wort LIKE 'd''Versiounsnummer%' 
    COLLATE Latin1_General_CS_AS
    

    Thursday, January 21, 2016 3:39 PM
  • User2146470223 posted

    you mean using only one collate? Will it then still apply to all of the strings I'm looking for?

    Thursday, January 21, 2016 4:15 PM
  • User2146470223 posted

    well here is the parametrized version which throws this error:

    System.IndexOutOfRangeException: wort

                                        SQL1.Parameters.Add("@Tempit2", SqlDbType.NVarChar, -1).Value = Tempit2
                                        SQL1.Parameters.Add("@Tempit2b", SqlDbType.NVarChar, -1).Value = Replace(Trim(Tempit2b), "''", "'")
                                        SQL1.Parameters.Add("@str_suchlike", SqlDbType.NVarChar, -1).Value = Replace(Trim(str_suchlike), "''", "'")
                                        SQL1.Parameters.Add("@str_suchlikeb", SqlDbType.NVarChar, -1).Value = Replace(Trim(str_suchlikeb), "''", "'")
    
                                        SQL1.CommandText = "SELECT wort FROM LUX WHERE wort COLLATE Latin1_General_CS_AS = @Tempit2 OR wort COLLATE Latin1_General_CS_AS = @Tempit2b  OR wort like @str_suchlike COLLATE Latin1_General_CS_AS OR wort LIKE @str_suchlikeb COLLATE Latin1_General_CS_AS ORDER BY wort DESC"
    
                                    SQL1 = New SqlCommand(sqlt, axsqlCon1)
                                    axsqlCon1.Open()
                                    rs1 = SQL1.ExecuteReader()

    While rs1.Read str_wort = rs1("wort")
     …
     …

    EDIT:

    str_wort = rs1.GetValue(0) made this one work again

    Thursday, January 21, 2016 9:09 PM
  • User77042963 posted

    You need to add wildcard with your parameter.

    Also you need to have code to check whether your datareader is empty or not.

    Try this:

    SQL1.CommandText = "SELECT wort FROM LUX WHERE wort = @Tempit2 OR wort = @Tempit2b OR wort like '%'+ @str_suchlike +'%' OR wort LIKE '%'+ @str_suchlikeb + '%' ORDER BY wort DESC COLLATE Latin1_General_CS_AS"

    Here is a sample from MSDN:

    https://msdn.microsoft.com/library/dw70f090%28v=vs.100%29.aspx?f=255&MSPPError=-2147217396

    Thursday, January 21, 2016 9:23 PM
  • User753101303 posted

    Hi,

    As pointed by others you can use parameters (and it does work with LIKE). To me your statemenrt escape just the first string. The 3 other strings are NOT escaped.

    Thursday, January 21, 2016 9:43 PM
  • User2146470223 posted

    doooh... thanks forgot to add the +'%' at the end of the like parts (there are none at the beginning)

    Thursday, January 21, 2016 9:44 PM
  • User2146470223 posted

    well no, when using parametrized queries I don't get the correct result at all. strange enough it returns a value from a completely different table that is not even queried for.

    isn't there a way to solve it without parameters? It's the same problem I had, when I decided to switch away from parameters last time.

    Thursday, January 21, 2016 10:28 PM
  • User-219423983 posted

    Hi Translating-IT,

    strange enough it returns a value from a completely different table that is not even queried for.

    It’s quite strange if you query the datable “LUX”, but the result not comes from the table ”LUX”. I think you could first refer to the following thread to get the final Sql command string to check whether it’s right or not.

    http://stackoverflow.com/questions/265192/get-the-generated-sql-statement-from-a-sqlcommand-object

    Then, you could run your expected sql string to your database. After that, you can compare with the two results to check whether the C# result is right or not.

    About why using sql command parameters, you could refer to this below thread. In this thread, it provides a demo to explain a SQL Injection attacks demo. So, as suggested above, using parameters is safe and better choice for you.

    http://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements

    Best Regards,

    Weibo Zhang

    Friday, January 22, 2016 5:24 AM
  • User753101303 posted

    Parameters are working fine. You just have a problem in how they are used (or maybve it's not even related to parameters). The VB code you shown does NOT generate the SQL string you shown: it escape only the first string value but not the other 3 values.

    I would suggest to build a thin API so that you don't haveto htink about escapîng again and again each value plus you'll be able to swtich to paramters later easily if you want. It could be Something such as :

    yourFinction("SELECT .. WHERE Col={0} AND OtherCol={1}",value1,value2) for example and so all the mundane stuff is done behind the scene once for all.

     

    Friday, January 22, 2016 8:06 AM
  • User2146470223 posted

    sorry for the late reply but did not get to programming for quite some time …

    @Weibo

    I know about SQL injection but where I'm programming right now there will be no sql injection and if anyone would get access there, he would not need injection anymore anyway.

    at the moment I only want to get my app running the way it is supposed to. As it is quite complex and I don't know yet how exactly to get where I need to get (the goal is very clear and I just have to find the way there) so I have to change my code from scratch quite often as I at some point realize that I could use better methods or solutions than the ones I have. So using parameters or not is the least of my worries. btw: I had everything coded with parameters using stored procedures and it generated more issues at one point so I decided to drop these and use normal queries until I get close to the wished result, when I would need to publish everything and would only need to make everything as safe as possible.

    in the meantime I switched to SQL Server 2016 and after some time back to using parameters. Since the switch to 2016 the issue was not repro anymore. So we could close this thread but I don't know which post to set mark as solution as it was not really solved.

    @Patrice

    well the code I have generates exactly that query and that's why I don't understand why it is not working. using the very same query in server managemen studio (copying it there) it works perfectly but not from the app and I don't know why because all quotes are correctly quoted out.

    The escaping is done automatically by the app and up to now it never failed and as it was not changed for quite some months I don't know why it should stop working and not escape the way it always did.

    cheers,

    Pascal

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 19, 2016 10:51 PM