[RESOLVED] stripping the ' character in a select statement

dirkadirka

ok, I really need help now...I know how to strip the ' char from an already retrieved field..

$stripnha = str_replace("'", "", $row_Recordset1['NHA']);

however, when you run the select statement, how do you account for the ' character??

$stripnha = str_replace("'", "", $row_Recordset1['NHA']);
$query_Recordset2 = "SELECT resumes.RESUME_ID, resumes.FULL_NAME FROM resumes, facilities WHERE resumes.FULL_NAME = '"  . $stripnha . "'";

how do I strip the ' character from the resumes.FULL_NAME after the where clause within the select statement? is that possible?

thanks for any help. 🙂

dirkadirka

does my question not make sense? Im kinda lost here.

bogu

U don't need to strip it, just use addslashes, and when u need to echo your result use stripslashes ...

U strip it because it gives u an error in mysql statement, but using this it wont give u any trouble.

dirkadirka

I'm a little confused on the application of addslashes in this case...not every resume.full_name will have the ' character in them, I only want to remove the ones that exist.

Where in the select statement would I apply it?

Thanks so much, you are a life saver!!!!

laserlight

U don't need to strip it, just use addslashes, and when u need to echo your result use stripslashes ...

That is not advisable. To begin with, there is no need to use stripslashes on the data retrieved from the database, since they would not be in escaped form.

Use the appropriate escaping mechanism/function for your database interface. In the case of the old MySQL interface, this may mean using [man]mysql_real_escape_string/man. Note that the escaping mechanism for other database systems usually involve doubling the single quote rather than prepending a backslash.

If possible, use the prepared statement interface as provided by the mysqli and PDO interfaces.

bogu

I have never encounter any problems with addslashed and stripslashes, but laserlight is right, u should use mysql_real_escape_string().

dirkadirka

Im using 3.43.29 of mysql, and 4.3.11 PHP...

cannot upgrade.

Im more confused than ever now, how would I escape the ' char from the retrieved data in the select statement? I'll keep looking at the possible commands, but I'm not sure I'll understand how to implement the solution without seeing a good example.

thanks!

laserlight

how would I escape the ' char from the retrieved data in the select statement?

Well, to start with, you should ask yourself why the escaping is needed.

Take for example this SQL statement:

SELECT columnname FROM tablename WHERE columnname='$value'

Suppose $value contained "bogu's text". Now the SQL statement would be sent to the database for parsing as:

SELECT columnname FROM tablename WHERE columnname='bogu's text';

The database's SQL parser will choke on this, since it sees the SQL statement as:

SELECT columnname FROM tablename WHERE columnname='bogu'

with a mysterious trailing string "s text'" that does not seem to belong anywhere.

With this, we can conclude that we should escape when constructing SQL statements. There is then no need to escape again when retrieving from the database, since the database's SQL parser would have stripped away the escaping characters, leaving us with the original text.

In your code snippet, we would then write:

$query_Recordset2 = "SELECT resumes.RESUME_ID, resumes.FULL_NAME
	FROM resumes, facilities
	WHERE resumes.FULL_NAME = '"  . mysql_real_escape_string($stripnha) . "'";

without using str_replace() on $stripnha.

dirkadirka

I love you Laserlight, marry me? 😛