Re: Trouble escaping / Misc nightmare

Ian Rastall (idrastall@xxxxxxxxx) wrote:
: On 28 Jun 2005 19:01:02 -0800, yf110@xxxxxxxxxxxxxxxxxxx (Malcolm
: Dew-Jones) wrote:

: > $value = "this', 'will mess things up";
: >
: > $sql1 = "insert into tbl values ('$value')"; # inserts 2 columns!
: >
: > $esc_value = mysql_escape_string($value);
: >
: > $sql2 = "insert into tbl values ('$esc_value')"; # this is correct

: Hi Malcolm. From my palty understanding of PHP, :-), it seems as if
: you're talking about inserting values into a database.

My examples were inserts, but any data put into a query string should be
escaped so the string is interpretted correctly. The escaping ensures the
string contains the original data (not the escaped data) when it arrives
at the database.

e.g.
$id = "somebody's data";
$escaped_id = mysql_escape_string($id);
$sql = "select * from tbl where id='$escaped_id'";



: The values I'm
: worried about are already in there. I use phpMyAdmin to build my
: database, and I can see when I browse the table in question that the
: apostrophes aren't causing a problem.

It's hard to know with 100% certainty what is in the database because
phpMyAdmin will have had to escape the data to display it. I would want
to use mysql> to confirm the characters are what I think they are. (And I
wonder if the character set can make a difference, the character may look
like a ' but is it the same binary value as your data that looks like a '
(?)


: The HTML/PHP prints out the string just fine on the web page
: (apostrophe and all).

My question is whether the apostrophe you see is stored as an apostrophe
(') or as something else, such as ' which will end up looking
correct in the browser, depending on what phpMyAdmin does when displaying
the data.


: This is a string which can be clicked on, at
: which point the browser sends the text of the link back to the
: database, asking for a record with that string. It doesn't find it,
: because the apostrophe is messing things up somehow.

The query from the browser must correctly encode the ' too. Again, the
string could actually be something else and just look like a '.


I am thinking I would open a temp text file and store all the values you
are receiving at each stage you use them, so you can examine the bytes
later in a text editor.

# PSEUDO code

fp = fopen("/tmp/my-file.txt","w");

$album = $_GET['album'];
printf(fp,'$album = $_GET['album'] => [%s]\n",$album);

$album=mysql_escape_string($album);
printf(fp,'mysql_escaped $album = [%s]\n",$album);

$sql = "select * from .etc.etc ";
printf(fp,'the sql string=[%s]\n",$sql);

# etc, also dump the data from the database same way


: Nothing seems to help, though, and this is probably a simple thing (to
: someone else, at least). :-) Sorry if I'm taking up too much space on
: the ng. Just developing a real headache over this.

The niggly little things are always the trickiest.


--

This space not for rent.
.

Relevant Pages

  • Re: HTML doc save dialog
    ... After getting the string from database do not set the string to ... > and end html tags. ... In the document complete event of browser, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: A little Rolodex [revised]
    ... including alpha sort and searching for any embedded string, ... In this application, a database is a directory, ... you may optionally provide any alternate UCASE program, ... NN -> first store NN as key length ...
    (comp.sys.hp48)
  • Re: return multiple rows from sql statement
    ... strings from input values is almost certainly a safe path to SQL ... Also, being a MySQL function, it knows what MySQL needs or uses. ... All characters that are entered in the fields make their way into the database unaltered. ... The insert of what surprisinlgly was NOT a syntax error, but a string called "mysql_insert_id" into an integer field resulted in the value zero being put in. ...
    (comp.lang.php)
  • Re: Code to delete/unlink Linked tables
    ... Public intLinkODBCTables As Variant, intLinkDB2Tables As Variant ... Public strLinkBackendDB As String, strLinkDSNname As String, strLinkLibName ... ' MsgBox "This database is in MDE format...I will delete/recreate ODBC ... Public Sub fncLinkDB2Table() ...
    (microsoft.public.access.modulesdaovba)
  • Re: Establish connection with and transferring data to Access
    ... The specific problem with your code is that you need to quote string ... Dim vConnection As New ADODB.Connection ... Experiment with the object model or check the documentation for the ... Doug's code opens and writes to an existing database. ...
    (microsoft.public.word.vba.general)