-2

I have troubles fetching data from a MySQL database when the text inside a VARCHAR field is surrounded by angle brackets ('<' and '>' characters).

Let me be more specific. This is the table from which I'm fetching data:


    mysql> describe msgs;
    +---------------+---------------------+------+-----+---------+-------+
    | Field         | Type                | Null | Key | Default | Extra |
    +---------------+---------------------+------+-----+---------+-------+
    | partition_tag | int(11)             | NO   | PRI | 0       |       |
    | mail_id       | varbinary(16)       | NO   | PRI | NULL    |       |
    | secret_id     | varbinary(16)       | YES  |     |         |       |
    | am_id         | varchar(20)         | NO   |     | NULL    |       |
    | time_num      | int(10) unsigned    | NO   | MUL | NULL    |       |
    | time_iso      | char(16)            | NO   |     | NULL    |       |
    | sid           | bigint(20) unsigned | NO   | MUL | NULL    |       |
    | policy        | varchar(255)        | YES  |     |         |       |
    | client_addr   | varchar(255)        | YES  |     |         |       |
    | size          | int(10) unsigned    | NO   |     | NULL    |       |
    | originating   | char(1)             | NO   |     |         |       |
    | content       | char(1)             | YES  |     | NULL    |       |
    | quar_type     | char(1)             | YES  |     | NULL    |       |
    | quar_loc      | varbinary(255)      | YES  |     |         |       |
    | dsn_sent      | char(1)             | YES  |     | NULL    |       |
    | spam_level    | float               | YES  |     | NULL    |       |
    | message_id    | varchar(255)        | YES  | MUL |         |       |
    | from_addr     | varchar(255)        | YES  |     | NULL    |       |
    | subject       | varchar(255)        | YES  |     |         |       |
    | host          | varchar(255)        | NO   |     | NULL    |       |
    +---------------+---------------------+------+-----+---------+-------+

    mysql> select * from msgs LIMIT 1;
    +---------------+--------------+--------------+----------+------------+------------------+-----+--------+--------------+------+-------------+---------+-----------+--------------+----------+------------+-------------------------------+----------------------------------------+---------+--------------------+
    | partition_tag | mail_id      | secret_id    | am_id    | time_num   | time_iso         | sid | policy | client_addr  | size | originating | content | quar_type | quar_loc     | dsn_sent | spam_level | message_id                    | from_addr                              | subject | host               |
    +---------------+--------------+--------------+----------+------------+------------------+-----+--------+--------------+------+-------------+---------+-----------+--------------+----------+------------+-------------------------------+----------------------------------------+---------+--------------------+
    |             0 | 0qJcjCXZM8K3 | 7a8Q1_crCzuj | 02085-01 | 1365578237 | 20130410T071717Z |   4 | MYNETS | 172.31.255.5 | 1246 | Y           | V       | Q         | 0qJcjCXZM8K3 | N        |         -1 | <51651189.9080705@test.it>    | User Name <test@test.it>               |  test   |  localhost         |
    +---------------+--------------+--------------+----------+------------+------------------+-----+--------+--------------+------+-------------+---------+-----------+--------------+----------+------------+-------------------------------+----------------------------------------+---------+--------------------+

When executing a query via PHP MySQLi interface (eg: SELECT * from msgs), the from_addr field show only the first part (in this case "User Name") but not the text inside the angle brackets ("test@test.it", in this case).

At the moment, I worked around it by using a REPLACE statement inside mysql query, substituting the angle brackets with square brackets. However, I would like to know how to correctly query text inside angle brackets.

Thank you all.

Dharman
  • 30,962
  • 25
  • 85
  • 135
shodanshok
  • 167
  • 11
  • 1
    Are you outputting this in a HTML page? Because it'll be interpreted as a HTML tag if you are - it'll show up in the source code. You can do a str_replace on the output to swap `<` and `>` with `<` and `>` – andrewsi Apr 12 '13 at 17:38
  • use `mysql_real_escape_string()` function, it will help you – Sumit Bijvani Apr 12 '13 at 17:42
  • If you're loading this in PHP, look at this function: http://php.net/manual/en/function.htmlspecialchars.php . – Shawn Apr 12 '13 at 17:43

1 Answers1

1

The output of from_addr will be there if you are receiving results from your query.

Take a look at this example:

$string = '<hello world>';
echo $string;
echo htmlspecialchars('<hello world>');

Whereas, echo $string will show in the source code, but is being treated as a tag, and therefore not display "on screen".

Using the htmlspecialchars() will change < > to &lt; &gt; which will show "on screen".

It appears that you are using this field for email, so I would NOT recommend saving to the database with htmlspecialchars, as you want the emails to send properly.

If the above didn't answer your question fully, and you wish to query the database for an email rather than the "send name", I would recommend using LIKE:

SELECT * FROM `msgs` WHERE `from_addr` LIKE '<%INSERT_EMAIL_ADDRESS%>'.

Hope this helps.

Samuel Cook
  • 16,620
  • 7
  • 50
  • 62
  • Ok, problem solved :) – shodanshok Apr 12 '13 at 18:15
  • While I was already using htmlspecialchars to escape special characters, a logic error inside my PHP scripts cause this functions to never be called. Thank you all for your pacience and courtesy. – shodanshok Apr 12 '13 at 18:15