PHP writing to SQL....

[GFWilliams]

Hi all,

I'm working on the code for my computing A2 project and have come over an issue and because it is easter, I can't get hold of my teacher, so I hope someone can help me

I'm saving long 'articles' which are inputted in a PHP form and when I write the article to the PHP

The code is:

$query = "INSERT INTO article VALUES ('','$title','$para1','$para2','$para3')";

When I put the query into the MySQL Database, if I have a SQL keyword it will just show an error message. Is there a way that I can avoid this?
(you may be able to tell, but I'm quite new to coding..)

Thanks

George

[marshalla]

Your use of quotes inside the brackets needs some reworking to solve the problem.

[tribbles]

Be very wary of code like this - if those fields are taken from a user input, then they can perform SQL injection attacks on your database.

For example, if $title was:

'); drop table article; --'

Then you could end up in a whole world of trouble.

You really should get into the habit of using mysql_real_escape_string() to make sure it's been correctly quoted (and can't cause problems).

Your code should be:

$query = "INSERT INTO article VALUES ('','" . mysql_real_escape_string($title) . "','" . mysql_real_escape_string($para1) . "','" . mysql_real_escape_string($para2) . "','" . mysql_real_escape_string($para3) . "')";

It'll solve any quoting problems you're having with MySQL keywords.

[GFWilliams]

marshalla said:

Your use of quotes inside the brackets needs some reworking to solve the problem.

How so?

Also, is there a way that when it is called back up to display the article, if there is a break (new line) to automatically add a new line as opposed to having to type </br> into the box?

[GFWilliams]

tribbles said:

Be very wary of code like this - if those fields are taken from a user input, then they can perform SQL injection attacks on your database.

For example, if $title was:

'); drop table article; --'

Then you could end up in a whole world of trouble.

You really should get into the habit of using mysql_real_escape_string() to make sure it's been correctly quoted (and can't cause problems).

Your code should be:

$query = "INSERT INTO article VALUES ('','" . mysql_real_escape_string($title) . "','" . mysql_real_escape_string($para1) . "','" . mysql_real_escape_string($para2) . "','" . mysql_real_escape_string($para3) . "')";

It'll solve any quoting problems you're having with MySQL keywords.

Thank you very much indeed

I did have the variables running through this clean function:

function clean($input,$maxlen)
{
//cleans input before writing to database
//not cleaning data before running queries is a security risk. There is more we can do.
$input=substr($input,0,$maxlen);
$input=EscapeShellCmd($input);
return($input);
}

Edited by GFWilliams on Saturday 3rd April 15:20

[tribbles]

GFWilliams said:

marshalla said:

Your use of quotes inside the brackets needs some reworking to solve the problem.

How so?

Also, is there a way that when it is called back up to display the article, if there is a break (new line) to automatically add a new line as opposed to having to type </br> into the box?

From memory:

$para1 = str_replace("\n", "<br />", $para1);

[GFWilliams]

tribbles said:

GFWilliams said:

marshalla said:

Your use of quotes inside the brackets needs some reworking to solve the problem.

How so?

Also, is there a way that when it is called back up to display the article, if there is a break (new line) to automatically add a new line as opposed to having to type </br> into the box?

From memory:

$para1 = str_replace("\n", "<br />", $para1);

Thanks again

ETA: It worked

Edited by GFWilliams on Saturday 3rd April 15:23

[tribbles]

GFWilliams said:

I did have the variables running through this clean function:

function clean($input,$maxlen)
{
//cleans input before writing to database
//not cleaning data before running queries is a security risk. There is more we can do.
$input=substr($input,0,$maxlen);
$input=EscapeShellCmd($input);
return($input);
}

Edited by GFWilliams on Saturday 3rd April 15:20

I'm not 100% sure that's safe - I'd need to do some experiments, and it hinges on what a "pair" means in the PHP documentation. From the PHP documentation for it:

PHP docs said:

' and " are escaped only if they are not paired.

If a "pair" means "an even number in the input", then the following would happen:

Hello => Hello
Hello' => Hello\'
'Hello' => 'Hello'
'' => ''

If a "pair" means two consecutive, then the following would happen:

Hello => Hello
Hello' => Hello\'
'Hello' => \'Hello\'
'' => ''

If the former is correct, then it means that my example I'd given will not end up with the quotes escaped, and you'd have problems.

If the latter is correct, then you'll probably be okay - although it is escaping far more characters than are necessary. mysql_real_escape_string() is designed just for what mysql needs.

[GFWilliams]

tribbles said:

GFWilliams said:

I did have the variables running through this clean function:

function clean($input,$maxlen)
{
//cleans input before writing to database
//not cleaning data before running queries is a security risk. There is more we can do.
$input=substr($input,0,$maxlen);
$input=EscapeShellCmd($input);
return($input);
}

Edited by GFWilliams on Saturday 3rd April 15:20

I'm not 100% sure that's safe - I'd need to do some experiments, and it hinges on what a "pair" means in the PHP documentation. From the PHP documentation for it:

PHP docs said:

' and " are escaped only if they are not paired.

If a "pair" means "an even number in the input", then the following would happen:

Hello => Hello
Hello' => Hello\'
'Hello' => 'Hello'
'' => ''

If a "pair" means two consecutive, then the following would happen:

Hello => Hello
Hello' => Hello\'
'Hello' => \'Hello\'
'' => ''

If the former is correct, then it means that my example I'd given will not end up with the quotes escaped, and you'd have problems.

If the latter is correct, then you'll probably be okay - although it is escaping far more characters than are necessary. mysql_real_escape_string() is designed just for what mysql needs.

The code isn't actually mine (a snippet of code I nabbed from somewhere else), but I have left it in as it still cleans the length.. I have added mysql_real_escape_string() to that query and it is now working - thank you very much sir Now I'm going to add it to all the queries on the rest of the site!

[Pigeon]

tribbles said:

GFWilliams said:

marshalla said:

Your use of quotes inside the brackets needs some reworking to solve the problem.

How so?

Also, is there a way that when it is called back up to display the article, if there is a break (new line) to automatically add a new line as opposed to having to type </br> into the box?

From memory:

$para1 = str_replace("\n", "<br />", $para1);

Or use nl2br()

http://php.net/manual/en/function.nl2br.php

[tribbles]

Pigeon said:

Or use nl2br()

http://php.net/manual/en/function.nl2br.php

Hadn't come across that - you learn something new every day!

Although that's two for today - the other one was Don't trust the IDE to get dependencies right; if something looks odd, do a clean build.