Problem with storing data in mysql table.

[waveparticle]

Hiya everyone,

So I'm learning PHP from scratch by directly building a CMS platform. So far so good, everything is working fine from login to registration to even a voucher system.

Now my problem is kind of stupid but Google is not really helping.

When I'm trying to INSERT some data in my table and the data contains either ' or - nothing gets inserted in my table. I'm pretty sure these characters are sort of restricted for security reasons, but I need to store them, since the paragraph of text won't make sense without them.

Now the form has several input text and a textarea which needs to have a long paragraph. Everything could be perfect in all the input fields but if only 1 field happens to have ' or - the entry is not made in the database.

How do I get around this problem?

Thanks.

 

[Bar0n]

Your code is probably also extremely vulnerable to SQL injection attacks. The ' and - characters are parsed by SQL because you probably do something along the lines of the following in your code:

$unsafe_variable = $_POST['user_input']; 

mysql_query("INSERT INTO table (column) VALUES ('" . $unsafe_variable . "')");

See this link on how to fix both the security and erroneous parsing issues: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php

 

[koeksGHT]

use this before storing

http://www.w3schools.com/tags/ref_urlencode.asp

 

[Hamster]

You'll need code to escape your strings as well (turn single quotes into double single quotes etc). Not sure if PHP has something built in for you.

Maybe Bar0n's mysql_query() function does it

 

[semaphore]

Stop writing raw sql and use something like propel.

 

[LewSkannen]

Hiya everyone,

So I'm learning PHP from scratch by directly building a CMS platform. So far so good, everything is working fine from login to registration to even a voucher system.

Now my problem is kind of stupid but Google is not really helping.

When I'm trying to INSERT some data in my table and the data contains either ' or - nothing gets inserted in my table. I'm pretty sure these characters are sort of restricted for security reasons, but I need to store them, since the paragraph of text won't make sense without them.

Now the form has several input text and a textarea which needs to have a long paragraph. Everything could be perfect in all the input fields but if only 1 field happens to have ' or - the entry is not made in the database.

How do I get around this problem?

Thanks.

You need to make use of stripleashes.

Here is a very basic sample code you can use to store the information:

<?php	
mysql_connect("localhost", "[B]database_user[/B]", "[B]password[/B]") or die(mysql_error());
foreach ($_POST as $k => $a) {
    $_POST[$k] = mysql_real_escape_string($a);
	$_POST[$k] = strip_tags($a);
}
foreach ($_GET as $k => $a) {
    $_GET[$k] = mysql_real_escape_string($a);
	$_GET[$k] = strip_tags($a);
}
foreach ($_REQUEST as $k => $a) {
    $_REQUEST[$k] = mysql_real_escape_string($a);
	$_REQUEST[$k] = strip_tags($a);
}
mysql_select_db("[B]database_name[/B]") or die(mysql_error());

$[B]field1 [/B]= mysql_real_escape_string(trim($_POST['[B]field1[/B]']));
$[B]field2 [/B]= mysql_real_escape_string(trim($_POST['[B]field2[/B]']));

mysql_query("INSERT INTO [B]table [/B]SET [B]field1[/B]='$[B]field1[/B]', [B]field2[/B]='$[B]field2[/B]'") or die(mysql_error()); 
?>

 

[koeksGHT]

or you could do it simply like I said and use urlencode.

Changes ' to %27 and " to %22 and so forth safe for database storage of the funny characters.

And its 2 lines of code , one when adding and one when retrieving..


otherwise a basic base64 encrypt before storing makes sure no funny code is being inserted into database

 

[LewSkannen]

or you could do it simply like I said and use urlencode.

Changes ' to %27 and " to %22 and so forth safe for database storage of the funny characters.

And its 2 lines of code , one when adding and one when retrieving..


otherwise a basic base64 encrypt before storing makes sure no funny code is being inserted into database

The problem with Base64 encryption is the fact that it is very server-resource heavy. If you have a very busy website with a number of users that keep storing information to a database, the website will be tremendously slow and may even stop functioning at some point.

 

[waveparticle]

or you could do it simply like I said and use urlencode.

Changes ' to %27 and " to %22 and so forth safe for database storage of the funny characters.

And its 2 lines of code , one when adding and one when retrieving..


otherwise a basic base64 encrypt before storing makes sure no funny code is being inserted into database

The thing is I need to store it as is, because it will be a paragraph for description. So, it's really important that I get the ', - for punctuation purposes.

So should I just url encode the text, then send it to the database? And on retrieving it from the DB I decode it? My question here; is urlencode an appropriate function to use here?

The problem with Base64 encryption is the fact that it is very server-resource heavy. If you have a very busy website with a number of users that keep storing information to a database, the website will be tremendously slow and may even stop functioning at some point.

I read about that. In PHP 5.5.0, there is password_hash which is the favored way of encrypting passwords and I'm using that in my login system. It's really secure and it has something known as the cost. The latter is simply a number which will make the cracking way harder in terms of computational power and days to crack the pass in case some nutcase decides to do it. I just use it since I found it's easy to use and I thank the crypto/mathematician guys to make this work for newbies like me

 

[koeksGHT]

The problem with Base64 encryption is the fact that it is very server-resource heavy. If you have a very busy website with a number of users that keep storing information to a database, the website will be tremendously slow and may even stop functioning at some point.

yeah true, depends his needs

The thing is I need to store it as is, because it will be a paragraph for description. So, it's really important that I get the ', - for punctuation purposes.

So should I just url encode the text, then send it to the database? And on retrieving it from the DB I decode it? My question here; is urlencode an appropriate function to use here?


I read about that. In PHP 5.5.0, there is password_hash which is the favored way of encrypting passwords and I'm using that in my login system. It's really secure and it has something known as the cost. The latter is simple a number which will make the cracking way harder in terms of computational power and days to crack the pass in case some nutcase decides to do it.

it's a 2 way function, one on storage and one upon retrieval

http://php.net/urlencode
http://php.net/urldecode

for passwords MD5/other with a salt work well

 

[Bar0n]

The thing is I need to store it as is, because it will be a paragraph for description. So, it's really important that I get the ', - for punctuation purposes.

You use urlencode to store it, and when you retrieve it, you decode it again.

 

[waveparticle]

yeah true, depends his needs



it's a 2 way function, one on storage and one upon retrieval

http://php.net/urlencode
http://php.net/urldecode

for passwords MD5/other with a salt work well

You use urlencode to store it, and when you retrieve it, you decode it again.

Works like a charm!
Thanks guys

 

[froot]

it's a 2 way function, one on storage and one upon retrieval

http://php.net/urlencode
http://php.net/urldecode

for passwords MD5/other with a salt work well

Yeah I was waiting for someone to mention that you need to use urldecode too. I've made that mistake in the past (as a newbie) and couldn't figure out why my outputs were poked.

 

[waveparticle]

Yeah I was waiting for someone to mention that you need to use urldecode too. I've made that mistake in the past (as a newbie) and couldn't figure out why my outputs were poked.

It's amazing how much we can learn from other people. That's why I prefer asking questions at times than just looking at the manual. There are so many ways of actually making something that sometimes we skip the simplest and most efficient way of doing things.

 

[froot]

It's amazing how much we can learn from other people. That's why I prefer asking questions at times than just looking at the manual. There are so many ways of actually making something that sometimes we skip the simplest and most efficient way of doing things.

Indeed.
Absolutely everything I know about PHP was from Googling tutorials/how-to's and asking people for advice.

 

[_kabal_]

Just use PDO and prepared statements with parameterized queries

And base64 is not an encryption scheme

 

[waveparticle]

Just use PDO and prepared statements with parameterized queries

And base64 is not an encryption scheme

I have been having a hard time with prepared statements due to the fact I'm new and not used to OOPHP yet. However, I tried the mysqli prepared statement in procedural style but it gets complicated. I'm still trying to implement it in my login/registration system since the current one I use is vulnerable according to what I read.


If you have any tutorials which explain PDO or mysqli prepared statements in procedural style(at least for the time being) please let me know.


I use password_hash function which is available in PHP 5.5.0 >= but there is a guy ircmaxell who made password_compat which can be used for lower versions, and it's really a good form of encryption. Never used base64, but I may use it for the sake of learning in the future.

 

[_kabal_]

I have been having a hard time with prepared statements due to the fact I'm new and not used to OOPHP yet. However, I tried the mysqli prepared statement in procedural style but it gets complicated. I'm still trying to implement it in my login/registration system since the current one I use is vulnerable according to what I read.


If you have any tutorials which explain PDO or mysqli prepared statements in procedural style(at least for the time being) please let me know.


I use password_hash function which is available in PHP 5.5.0 >= but there is a guy ircmaxell who made password_compat which can be used for lower versions, and it's really a good form of encryption. Never used base64, but I may use it for the sake of learning in the future.

sorry, I dont do php, but I am sure there are many resources, just search stack overflow.

password hashing isnt encryption. it is a one way operation, that cannot be "decryped".
you compare the hash of the supplied password with the persisted hash to determine if login should proceed, instead of decrypting the hash, and comparing it to the supplied password.