Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Single quote handling in a SQL string

I have an application where the values in the text field are sent to the database.

For example I have a form with one field (text box). When I press Ok button then the content of the text field is inserted as a record into a table. I'm just trimming and extracting the text box's text into variable and passing it to my SQL string.

The problem is that whenever something like "It's" or "Friend's" the single quote is identified as the end of string. In Delphi I have seen something like QuotedString to avoide this. Any ideas from you?

like image 219
JCTLK Avatar asked Jan 10 '11 07:01

JCTLK


People also ask

How do you quote a string in SQL?

QUOTE() : This function in MySQL is used to return a result that can be used as a properly escaped data value in an SQL statement. The string is returned enclosed by single quotation marks and with each instance of backslash (\), single quote ('), ASCII NULL, and Control+Z preceded by a backslash.

Can you use single quotes for string?

If you use single quotes to create a string, you can not use single quotes within that string without escaping them using a backslash ( \ ). The same theory applies to double quotes, and you have to use a backslash to escape any double quotes inside double quotes.

How do you handle a single quote in a statement?

Because a single quote is used for indicating the start and end of a string; you need to escape it. The short answer is to use two single quotes - '' - in order for an SQL database to store the value as ' .

Can I use quotes in SQL?

Single quotes are used to indicate the beginning and end of a string in SQL. Double quotes generally aren't used in SQL, but that can vary from database to database. Stick to using single quotes.


1 Answers

Don't ever build SQL statements like that, it's very unsafe (read this). Use parameters, i.e:

var command = new SqlCommand("select * from person where firstname = @firstname");
SqlParameter param  = new SqlParameter();
param.ParameterName = "@firstname";
param.Value         = "testing12'3";
command.Parameters.Add(param);
like image 120
Rob Avatar answered Sep 23 '22 14:09

Rob