1

I have a php page, with a simple form with some text areas within.

<textarea rows="4" name="issue" style="width:90%;"></textarea>

The form is submitted as a POST to another php page. I capture it as:

$ins_issue = nl2br($_POST['issue']);

I then write it to MySQL with an insert statement. (which is not working for all special characters. Commas for example break the query)

$ins_query = ("INSERT INTO data1 (
    p_key_project,
    category,
    issue, 
    proposed_resolution,
    action_items,
    owner,
    status,
    archived
) VALUES (
    '$passed_key',
    '$cat_1',
    '$ins_issue',
    '$ins_resolution',
    '$ins_action',
    '$ins_owner',
    '$ins_status',
    'n'
)");

What I'm trying to do is capture ALL special characters that are entered (spaces, line breaks, 's, colons, semi colons, etc ....) and write them to MySQL (I choose TEXT as the datatype in MySQL when I setup the table.)

When I then do a select from MySQL, and pre-populate the text area on the page, I want ALL characters to show up. Line breaks to be there, etc ....

What is the best way to accomplish this so that Anything that someone might paste into the textarea remains 100% intact and exact?

Phil
  • 157,677
  • 23
  • 242
  • 245
Vacek
  • 179
  • 1
  • 12
  • Prepare statements and bound parameters on the way in, HTML encoding on the way out (see `htmlspecialchars()`) – Phil Mar 14 '17 at 22:23
  • 2
    You should display a message on your website saying: "Hi there, I am not using [prepared statements](http://php.net/manual/en/pdo.prepared-statements.php) so my entire database is in the public domain." – Xorifelse Mar 14 '17 at 22:23
  • 4
    @Xorifelse I prefer `SQL Injection Test Tool` – Phil Mar 14 '17 at 22:25
  • nice title :) this is something I'm fiddling with on my laptop... nothing public about it. so if you'd humour me, I'm trying to learn along the way. – Vacek Mar 14 '17 at 22:27
  • @Phil `
    It's Not Escaped!
    ` makes XSS issues impossible to ignore. It'll make your screen red. The emoji can test for UTF8-MB4.     – tadman Mar 14 '17 at 22:27
  • @Phil I am really thinking about creating a SO comment generator website :), Vacek, It's good you can take it with a grain of salt, but this is very important to learn. – Xorifelse Mar 14 '17 at 22:31
  • Commas are breaking the query because your query is vulnerable to SQL Injections. Look into using [PDO and prepared statements](https://phpdelusions.net/pdo#query). It's really not that difficult, and best to learn now than get set in your ways doing things the wrong way. – Lucas Krupinski Mar 14 '17 at 22:35
  • @LucasKrupinski I gotta be honest with you, I don't see how a `,` could break this query. Only a `'` can. – Xorifelse Mar 14 '17 at 22:38
  • @Xorifelse - OP is saying that some special characters break it. It wouldn't break at all if it were using a prepared statement, so my guess is OP is just saying its their hypothesis that its the comma, but there could have been a `'` in there as well. – Lucas Krupinski Mar 14 '17 at 22:40
  • @LucasKrupinski Humans tell lies, code doesn't. No offence to Vacek, it would just be an interpretation of what he thought is happening, which is absolutely and perfectly fine to add to the question, it shows he has done some effort to diagnose in which I should probably make the vote neutral. I am saying this to you Lucas as you tend to be helpfull to expect what most questions are like. – Xorifelse Mar 14 '17 at 22:44
  • 2
    you are correct, apologies. it is ' that breaks it. I'm off to change everything to prepared statements with binding. My question taught me a lot (of what not to do) in 5 minutes ... so thanks to everyone for pointing out the obvious disaster in the making. And yes, take things with a grain of salt is what more people should do :) until it becomes a security risk then its serious. Thanks everyone. – Vacek Mar 14 '17 at 22:47
  • 1
    @Vacek A quick fix is `addslashes()`, but please.. learn prepared statements, which you already stated. my bad. Again, if you want to pick up prepared statements i recon PDO, not mysqli, their dual API is confusing the hell out of me to this very day, with over 10 years of experience in PHP. – Xorifelse Mar 14 '17 at 22:48

0 Answers0