Insert string that contains a single quote

[venturemc]

Another stupid newb question that I've been fighting for 3 days now. Searched thropugh several books and sevferal pages of past posts, but could not find an answer.....

 

PHP5.3, mySQL 5.1.37

 

I'm doing a simple insert into statment, using variables in the VALUE clause rather than literals. If I have a single quote in one of the strings, the insert bombs.

 

I've tried every variation of quotes, single quotes (in and around the variable) and escape characters and it still bombs when it encounters a single quote in the variable.

 

if (mysqli_error($dbc))

  echo 'Connect:' . mysqli_error($dbc);

 

//connects fine

$q [/]= "INSERT INTO table (fld1, fld2)" [/].

" VALUES('[/]$in[/][1[/]]','[/]$in[/][2[/]]'[/])"[/];

 

//works fine if no single quotes within variables

 

I've altered variable contents that have single quotes in many ways:

$in[1] = can't (original value grabbed from form via $_POST)

 

alterations (that I can rmember trying):

can\'t

'can\'t'

"can\'t"

"can't"

" ' " . can\'t . " ' "

' " ' . can\'t . " ' "

 

I'm printing the variable contents to the screen before and after I mess with them and they're transforming to what I'm asking (at least that part of my syntax is working).

 

// variations of syntax attempted in the VALUES clause:

Original: " VALUES('[/]$in[/][1[/]]','[/]$in[/][2[/]]'[/])"[/];

" VALUES(" '  . $in[1] . ' " ," ' . $in[2] . ' ")" ; [/]

' VALUES(' " . $in[1]. ' ", ' " . $in[2]. ' ")";[/]

 

//probably a few others that I can't remember...

 

//The insert string looks like this as printed to screen before the insert statement (the variable contains: Can\'t  for this result and the values clause has '$in[1]' syntax)

 

INSERT INTO table (field1, field2) VALUES('Can't', 'other string')

//here's the error

Error:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't', 'other string' ......

 

//the 't is the end of can't [/] 

what I'm looking for is a way to be able to INSERT a string that contains a character that needs an escape character. My preference would be a solution that's universal (If possible) across all PHP versions. 

 

 

[oni-kun]

For safety of someone injecting queries into your database, you must always use precautions such as mysql_real_escape_string.

 

This will make 'can't' into 'can\'t', the extra slash 'escaping' the single quote so it won't mess with your query.

[venturemc]

Thanks for the info onthat function, but it didn't solve my problem....