[RESOLVED] SQL statement and inverted commas

chrislead

Heys,

I have this script:

$sql = "UPDATE tbl_pistols_image 
				SET im_album_id = $albumId, 
				    im_title = '$imgTitle', 
					im_description = '$imgDesc', 
					im_image = $image, 
					im_thumbnail = $thumbnail, 
					im_date = NOW()
				WHERE im_id = $imgId";

Ok, so we are updating a row here.

However if the user enters any inverted commas into the text field that are being updated, I receive this error:

Error, update image failed : 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 'Peniet Pistol'', im_image = im_image, im_thumbnail

Using quotes works fine and inserts them fine, it just seems to be any inverted commas, and I guess they are altering the SQL syntax when executed.

Anyone know any fixes for this problem to allow users to submit inverted commas as part of the text?

Thanks in adv.
Chris.

Piranha

I have no idea what a inverted comma is, can you please post one of those to let me know.

Anyway, using mysql_real_escape_string for mysql and simular functions for other databases will probably remove that problem.

chrislead

me appologies,

inverted comma: ( ' )
in the form: 'sometext'

I will take a look into your suggestion in the meantime

//////////////////////////////

Ok had a look at mysql_real_escape_string, and I am now confused:

if I submit the following string: '' (that is 2 inverted commas)

The query runs with no errors, ok great, but only inserts 1 inverted comma, and discards the 2nd.....?

What is more, If I then update this record with: 'test' (i.e. adding actualy text) I receive the error again!

I also tryed submitting this: '''''''''''''' (so loads of inverted commas) The query ran with no errors but again only submitted 4 of the inverted commas!!!! strange!

Any other help would be appreciated here.

Piranha

Ok, then it is mysql_real_escape_string that you should use. Use it something like below, and use it in EVERY variable that you get from the outside to avoid SQL injection. And use it in EVERY query as well.

$sql = sprintf("SELECT id, name, date
FROM table
WHERE id = '%s'
OR name = '%s'
OR date = '%s'",
mysql_real_escape_string($_POST['id']),
mysql_real_escape_string($_POST['name']),
mysql_real_escape_string($_POST['date']));

coolteamlead

Hi,

What i can assume is the problem with the datatype and length of the col

Rest i will paste the code what i have tried and is working fine by adding '','test',''''''''

So try the below listed code or check the table as i have used varchar field with 50 length

test.php

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE> New Document </TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="">
<META NAME="Keywords" CONTENT="">
<META NAME="Description" CONTENT="">
</HEAD>
<BODY>
<FORM METHOD=POST ACTION="a.php">
<TEXTAREA NAME="abc" ROWS="" COLS=""></TEXTAREA>
<INPUT TYPE="submit">
</FORM>
</BODY>
</HTML>

and a.php

<?php
$conn = mysql_connect("localhost", "root", "");
mysql_select_db("test");
$abc = mysql_real_escape_string($_POST['abc']);
echo $query = "insert into te values ('".$abc."')";
mysql_query($query, $conn);
?>

and it is going fine,
Best of luck

chrislead

Thanks for your help, the above worked perfectly!

Cheers again.