0

Usually i use this code bellow to check if the name in database and it working good

        da = New SqlDataAdapter("select NameOfMov From MovTable where NameOfMov = '" & Trim(NameSearchTB.Text) & "'", sqlcon)
    da.Fill(MovieSearchdt)

but if the name that i check for it contain character like this (') like (men's) the project stop and give this error msg:- System.Data.SqlClient.SqlException: 'Incorrect syntax near 's'. Unclosed quotation mark after the character string ''.'

how can i fix this code please ?

ZaerAllail
  • 5
  • 5
  • 4
    You need to start using database parameters. That'll reduce the chance of a sql injection attack, improve your performance, and resolve the issue you're having. – Andrew Mortimer Apr 06 '20 at 10:28
  • 1
    ... and have a look here: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/dataadapter-parameters – Sebastian Brosch Apr 06 '20 at 10:33
  • 2
    This is because the data is not escaped or parameterised correctly. This flaw also means your code is vulnerable to SQL Injection attacks, which is a serious security risk. You need to prevent these syntax errors, and also prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks,**Never** insert unsanitised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. See the links already posted for examples of how to resolve it. – ADyson Apr 06 '20 at 10:34
  • You need to use '' (two single quotes) for using ' (single quote). e.g. for time format we can use "yyyy-MM-dd''T''HH:mm:ss.SSS''Z''" here we want 'T' and 'Z' in single quote. – Shivaji Mutkule Apr 06 '20 at 10:46
  • 1
    Ask yourself, what would be the command string when you enter `men's`? – Hans Kesting Apr 06 '20 at 11:23
  • 1
    Indeed, you've discovered the exact problem that is the root cause of every SQL injection hack in the world. Right now, it's relatively benign - you're just getting a crash because the title is `men's` but can you imagine what happens if a user types `' OR 'a' = 'a` into the NameSearchTB.Text? Now you're starting to understand [the bad things that can happen](https://coar.risc.anl.gov/consequences-of-sql-injection-attacks/) when you give other people the ability to type SQL into your program and have your program run it unwittingly – Caius Jard Apr 06 '20 at 12:14
  • 1
    @ShivajiMutkule That is a poor suggestion and will still leave it open to SQL injection attacks. – Andrew Morton Apr 06 '20 at 12:14
  • @AndrewMorton While I agree that parameters are the way to go, I have never found an example on how doubling the single qoutes leaves the door open for sql incjection (when using sql server). An example would be very interesting. – MatSnow Apr 06 '20 at 12:32
  • 2
    @MatSnow [How can sanitation that escapes single quotes be defeated by SQL injection in SQL Server?](https://stackoverflow.com/q/15537368/1115360) – Andrew Morton Apr 06 '20 at 12:37
  • I tried all of these solutions but nothing worked. So please, my wrong query code is included in the question and I need someone who knows what modification is necessary for it to work. Also, to make clear, I want a query code not saving code! – ZaerAllail Apr 07 '20 at 15:34
  • I finally found the solution. I just needed to add .ToString().Replace("'", "''") after the text. But I still don't understand how it works. Can someone explain briefly why? Thanks – ZaerAllail Apr 07 '20 at 16:07
  • @ZaerAllail - No, replacing quotes is not the *right* solution. For that you need to use parameters instead of injecting user-specified text into your sql command (look up "sql injection"). As to why it works for now, inspect the command string after you built it. Pay attention to all the quotes. – Hans Kesting Apr 08 '20 at 07:25
  • ok @Hans Kesting i like to do the right way , so please just give me simple code for query code with parameters and i will happy for that ! – ZaerAllail Apr 08 '20 at 17:53
  • @ZaerAllail I can't post an answer because the question is closed. What you need to do is pass an SqlCommand to the DataAdapter instead of the text. You can add the parameter to the SqlCommand, and you have to attach the connection to the SqlCommand, not the data adapter. – Andrew Morton Apr 10 '20 at 11:43
  • @ZaerAllail `Dim connString = "your connection string"` `Dim sql = "SELECT NameOfMov FROM MovTable WHERE NameOfMov = @Name"` `Using conn As New SqlConnection(connString)` `Dim cmd As New SqlCommand(sql, conn)` `cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@Name", .SqlDbType = SqlDbType.NVarChar, .Size = 255, .Value = Trim(NameSearchTB.Text)})` `Dim da As New SqlDataAdapter(cmd)` `da.Fill(movieSearchDt)` `End Using` – Andrew Morton Apr 10 '20 at 11:43
  • 1
    @Andrew Morton - thank you, its working good . – ZaerAllail Apr 10 '20 at 13:33
  • @ZaerAllail You're welcome :) I forgot to mention that you will need to change the `.SqlDbType` and `.Size` to match the column type in the database. And now you can change all the other places to use similar code to make it safe :) – Andrew Morton Apr 10 '20 at 13:37
  • @Andrew Morton - thank you for your interest, and i do it right & save after your help ! – ZaerAllail Apr 11 '20 at 04:12

0 Answers0