How to make a string literal in php sql

Question

I have a query like this. This one came from a text box. Because of the ' in it's it closes the string.

I don't want to trap it in the text box. Because I need to allow the text box to write anything the user want as reason.

I tried to put @ before the variable. But it seems it does not work like C# where putting @ before a string makes it literal.

I know this is also vulnerable to injection. But if the solution has also to do with injection might as well hit two birds in one stone. But if its possible to solve just the escaping the characters that will do.

$_GETVARS['txtReason'] = "'it's already there, in droves.'";

UPDATE tablename SET reason_for_exception='".$_GETVARS['txtReason']."' WHERE ID='1'

Can you show me how? Because the variable there will come from a variable. So the user can enter: it's or its based on his decision. — aozora, Jun 19 '15 at 06:44
how about putting the values of the string in a variable and sanitize the variable before putting it there.. — Sam Teng Wong, Jun 19 '15 at 06:44
Use http://php.net/manual/en/function.mysql-real-escape-string.php — test, Jun 19 '15 at 06:45

Chiragkumar Thakar · Answer 1 · 2015-06-19T06:51:39.530

I think you should try below Query, and it would be better to use Stored procedure, this type of issues will not be occurred there.

UPDATE tablename SET reason_for_exception='it''s already there, in droves.' WHERE ID='1'

or if you want to Continue with normal Query then store that string into some string variable and then try, might be helpful to you.

Like

$_GETVARS['txtReason' = "it's already there, in droves.";

UPDATE tablename SET reason_for_exception='".mysqli_real_escape_string($link,$_GETVARS['txtReason')."' WHERE ID='1'

score 1 · Accepted Answer · answered Jun 19 '15 at 06:47

1

$reason_for_exception = mysql_real_escape_string("your text here");

UPDATE tablename SET reason_for_exception= "$reason_for_exception" WHERE ID='1'

how about that?

answered Jun 19 '15 at 06:47

Sam Teng Wong

2,379
5
34
56

Thanks this is what I need. It added the needed / in the string. Will this also prevent sql injections? Or that is another story? – aozora Jun 19 '15 at 06:54
yes it will also.. but this is deprecated.. I think you need to go with mysqli. =) happy coding! – Sam Teng Wong Jun 19 '15 at 06:58

score 1 · Answer 3 · answered Jun 19 '15 at 06:47

for this wrap your content in mysqli_real_escape_string()

for reference Mysqli_real_escape_string

$_GETVARS['txtReason' = "it's already there, in droves.";
UPDATE tablename SET reason_for_exception='".mysqli_real_escape_string($link,$_GETVARS['txtReason')."' WHERE ID='1'

score 0 · Answer 4 · edited May 23 '17 at 11:52

0

You can use mysqli_real_escape_string to escape the string for SQL input.

Not sure what you're using to build your queries but you may want to look into using prepared statements if you're not already. How can I prevent SQL injection in PHP?

edited May 23 '17 at 11:52

Community

1
1

answered Jun 19 '15 at 06:48

rch

91
2
7

How to make a string literal in php sql

4 Answers4