2

As I was trying to add a quote (via a PHP/HTML form) to my site that was particularly long, I noticed that the longer quotes did not get added via the MySQL query. The shorter ones got added just fine.

This is not a problem with the length of the columns in my table, the quotes are most definitely shorter than the limit (which is VARCHAR(600)).

When I try to manually enter the quotes into the database from the MySQL command line, hitting enter after entering the query properly does not result in a new prompt (it goes to the next line of the query instead). The query never executes.

The only way I can get to a new prompt is to force exit out of the MySQL command line and re-opening it. Anyone know why this is happening?

EDIT: Here is my query. The first 3 values are irrelevant, I won't confuse anyone by explaining them. The last 2 are the source and the quote.

EDIT2: MySQL version is 5.5.8

THE QUERY THAT DOES NOT WORK

INSERT INTO quotes VALUES(NULL, 2, 20, 'http://www.mlb.com/news/article.jsp?ymd=20091104&content_id=7620238&vkey=news_nyy&fext=.jsp&c_id=nyy', 'He's the reason we're here. First of all, we wouldn't be in this stadium if it wasn't for him. We wouldn't have this group together if it wasn't for him. It's a special moment. We all wanted to win it for him.');

EXAMPLE OF A QUERY THAT WORKS

INSERT INTO quotes VALUES(NULL, 2, 20, 'http://www.mlb.com/news/article.jsp?ymd=20091104&content_id=7620238&vkey=news_nyy&fext=.jsp&c_id=nyy', 'I guess you could say that this is the best moment of my life right now," Matsui said. "If I were to look back, yes, this would be the best.');

To those downvoting, mind telling me why?

FINAL EDIT Got it... it's because of the single quotes in my quote. Thank you.

tnw
  • 13,521
  • 15
  • 70
  • 111
  • 2
    make sure you are using *;* after finishing the query. – Sourav Jun 21 '11 at 15:56
  • 2
    Could you post the query you're trying to run, including a quote that isn't working, for us to look at? – neezer Jun 21 '11 at 15:58
  • Also, can you maybe mention the MySQL version you have? MySQL up to and including version 4.2 only supported VARCHAR up to 255 characters, only MySQL 5 and up support longer VARCHAR's. – KilZone Jun 21 '11 at 16:00
  • @neezer Query posted. @KilZone Version posted. – tnw Jun 21 '11 at 16:03

6 Answers6

7

The following things could cause the MySQL console to think that you have not yet finished your query, and there is more to enter:

  • An unclosed quote, or backtick.
    • Usually due to the fact that you didn't escape a quote by doing \' (for single quotes) or \" (for double quotes).
  • No semi-colon at the end of the query.

Make sure you don't have either of those problems and the query should run.

Edit following poster's edit: Yeah, looking at your query, you need to escape quotes by replacing ' with \' whenever it is inside an item of data. PHP has a function to escape for you which is mysql_real_escape_string(). You could also use prepared statements, which prevents this problem. See this question for more information.

Community
  • 1
  • 1
EdoDodo
  • 8,220
  • 3
  • 24
  • 30
  • look at my additional edits, I have a shorter query just like it that works. Additionally, I tried `\'` and it gives me an error: `Unknown command '\''.` – tnw Jun 21 '11 at 16:06
  • 2
    The fixed first query should look like this: `INSERT INTO quotes VALUES(NULL, 2, 20, 'http://www.mlb.com/news/article.jsp?ymd=20091104&content_id=7620238&vkey=news_nyy&fext=.jsp&c_id=nyy', 'He\'s the reason we\'re here. First of all, we wouldn\'t be in this stadium if it wasn\'t for him. We wouldn\'t have this group together if it wasn\'t for him. It\'s a special moment. We all wanted to win it for him.');` - is that what you have? Remember that you should only replace `'` with `\'` inside the data. – EdoDodo Jun 21 '11 at 16:09
3

It's absolutely obvious, my friend ... you have single quotes in your last string, while using those as your string boundary ...

Raffael
  • 19,547
  • 15
  • 82
  • 160
2

When in the MySQL console, make sure you add a semicolon (;) to the end of the query. Otherwise, it will expect more to the query.

Joseph
  • 1,988
  • 14
  • 21
  • You need to escape the single quotes. So the quote in your non-working example would be: `'He\'s the reason we're here. First of all, we wouldn\'t be in this stadium if it wasn't for him. We wouldn\'t have this group together if it wasn\'t for him. It\'s a special moment. We all wanted to win it for him.'` – Joseph Jun 21 '11 at 16:11
2

You've got single quotes inside your string, which is encapsulated in single quotes.

You should be escaping your strings with mysql_real_escape_string (or something similar, like PDO): http://php.net/manual/en/function.mysql-real-escape-string.php

neezer
  • 19,720
  • 33
  • 121
  • 220
1

it sounds like you did not escape the ' or " characters in the quote properly. look for any ' or " or \ characters and put a \ before them.

Gidon Wise
  • 1,896
  • 1
  • 11
  • 11
1

You post no PHP code but your SQL suggests that you are omitting two basic steps:

  1. Use prepared statements to inject data into your queries (or proper escaping functions)
  2. Test the result of your DB calls and print/log error messages.
Álvaro González
  • 142,137
  • 41
  • 261
  • 360