0

Ok this is a new one for me. Basically I have a table articles:

id: int auto increment
title: varchar(200)
description: varchar(1000)
ctext: longtext
chtml: longtext

Now I do an insert into this table with mysql_query:

INSERT INTO articles
(title, description, ctext, chtml)
VALUES
('$title', '$description', '$text', '$html')

All values have been passed through mysql_escape_string().

The text and html here are roughly 50k in size (so I can't really post the fully query here).

Now, here's the problem: the query works. A new row is inserted. However the ctext and chtml columns are empty. This is MySQL 5.0.51a and PHP 5.2.8. No errors are raised of any kind as far as I can tell.

Now I dumped the query out to a file in /tmp and ran it with:

mysql -u username -p dbname < /tmp/query

Same thing.

I copy the query into Navicat and it... works.

So what on earth is going on?

Improve this question

2 Answers 2

Reset to default
2

Some random thoughts:

  • Have you tried controlling the text length see if it only fails at one point?
  • What kind of connection are you opening? Which driver?
  • Have you checked the encoding of your connection? Some invalid characters might be sent in.
  • Have you tried using parameters instead of mysql_escape_string?
  • Have you tried executing directly from the same file from Navicat instead of using the copy-paste? Again, might be related to an invalid character that's not passed through the copy-paste but was saved in the file.
  • Just to cover the basics we so often forget, how do you verify that the data is not inserted? I mean, how to you visualize it? You could have a line break that hides the first lines from 2 out of 3 means of visualization. Just a long shot, but I've seen it happen.

Addition: MySQL connections defaults to latin1, you need to use something like mysql_query("SET NAMES 'utf8'") to transfer unicode characters.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Text length: not yet. Connection: just mysql_connect(). Encoding: get back to you. Parameters: I originally used mysqli but its way too buggy. Direct from file: no but I will. Verify: in Navicat I can see it when it works (from the one example of this query that did work).
Have a look at PDO too, I'm having great results until now with it.
What about mysql_set_charset('utf8')? Not that I think there are any non-ASCII characters in here but I'll check. Maybe I'll filter out high characters.
Ok, will passing it through filter_var($s, FILTER_UNSAFE_RAW, FILTER_STRIP_HIGH_FLAG) seems to make it work actually.
I suspect it's working with Navicat because they were filtered in the copy-paste.
|
1

I'm not sure if this matters, but mysql_escape_string is deprecated and replaced by mysql_real_escape_string

Have you tried it with smaller text?

Improve this answer

5 Comments

mysql_real_escape_string makes no difference. Yes I have tried with smaller text and gotten it to work but afaik there's no practical limit on query size and it seems really bad that it silently just doesn't insert data.
Well do you check the return value of mysql_query? If it returns false, there was an error. Mysql in PHP doesn't throw any warnings by itself. Check the value of mysql_error after the insert.
Yes I do that on every query. I don't use mysql_query directly. I go through my own wrapper that checks mysql_error and logs an error if one occurs so I don't have to litter my code with those code blocks.
+1 While not specifically the problem, changing the charset to utf8 does solve my problem and this is probably necessary once I do change the charset so thanks for pointing it out.
No problem, congrats on solving your problem!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.