Sql - insert text with carriage returns

0
votes

In my application, i send a big text as a post parameter to the server. The text is like the code below:

{"objects":[{"type":"path","originX":"center","originY":"center","left":138,"top":250.25,"width":184,"hei ght":254,"fill":null,"overlayFill":null,"stroke":{"source":"function anonymous() {\n\n var squareWidth = 10, squareDistance = 2;\n\n var patternCanvas = fabric.document.createElement('canvas');\n
patternCanvas.width = patternCanvas.height = squareWidth + squareDistance;\n var ctx = patternCanvas.getContext('2d');\n\n ctx.fillStyle = \"#005E7A\";\n ctx.fillRect(0, 0, squareWidth, squareWidth);\n\n return patternCanvas;\n
\n}","repeat":"repeat","offsetX":0,"offsetY":0},"strokeWidth":15,"strokeDashArray":null,"strokeLineCap":"round","strokeLineJoin":"round","strokeMiterLimit":10,"scaleX":1,"scaleY":1,"angle":0,"flipX":false,"flipY":false,"opacity":1,"selectable":true,"hasControls":true,"hasBorders":true,"hasRotatingPoint":true,"transparentCorners":true,"perPixelTargetFind":false,"shadow":null,"visible":true,"clipTo":null,"path":[["M",69.5,0],["Q",69.5,0,70,0],["Q",70.5,0,70.75,0],["Q",71,0,71.5,....

As you there are carriage returns in it. An i want to insert this text into mysql table as a blob. But it's not successfull. I think the reason is carriage returns in it because other examples without carriage returns work well.

How can i succeed to insert this kind of a text to my table?

By the way, i'm using codeigniter cart class with db session and try to keep this text as cart item option.

2
phpmysqlsqlcodeigniter
Why don't you submit it as varchar instead of blob? - Dan Bracuk
Does it change anything? Because probably the problem is not the size of text, the carriage returns in it. - Yusuf Can Gürkan
Using query parameters solves a lot of problems like this also. - Dan Bracuk
Your problem is caused by the fact that you don't escape input (in this particular case backslashes are not escaped properly \n\n should look \\n\\n2), which leaves your code vulnerable to sql injections. You should've used prepared statements to avoid this altogether. - peterm
Thanks, so how i can escape these in php. - Yusuf Can Gürkan

2 Answers

0
votes

You have to understand how escaping works. If you put something escaped in a string like this:

s = "Hello\nthere";

...then the result will contain a REAL linefeed. The variable itself will look like "Hello" plus linefeed plus "there". Now if you hand this over to some sql, it will get the linefeed, not the backslash plus n, which would be the proper version of telling sql to insert a linefeed. No, instead you created an sql string with a real newline inside the quotes.

So you'll have to say "let's make a string that tells sql to insert a newline", and to do this, you have to tell the language (whichever you use) to make a string that makes a string that makes a linefeed. THIS IS WHY you'll have to escape what's already escaped. It's kinda "tell Bob to tell Claire to come here" thing.

0
votes

So I've seen the "how can I escape it in PHP" question twice from the OP, so here's how to escape in PHP using codeigniter:

First queries with CodeIgniter

You need to use query bindings to help ensure everything is cleaned up before it's run.

assume the following:

$sql = 'SELECT * FROM my_table WHERE first_name=? AND city=?';

Note the two question marks. These are placeholders for our input values.

When I do the following

$this->db->query($sql,array('Mike','Asheville'));

There is a 1-1 mapping for each value in the array to each ?, so the first ? will be replaced by Mike, and the second ? will be replaced by Ashevile. Both values will be escaped appropriately.