PHP Insert Into

[dreadrocky]

I can't seem to find an answer for my issue. Hopefully one of you can..

 

My issue is using textarea to insert data into my database. Anything past about 200 characters will not post. So I usually have to limit the amount. It is pretty useless in many occasions. But not only that, if I post too quickly like a chat window posting only a hundred characters at a time, will not post if I posted too quickly.

 

Any ideas or solutions are welcome, even if your not sure yourself. I might could make use of it. Thank you in advance..

 

So.. I used HTML forms, PHP Insert Into, and phpMyAdmin. Working on hostgator. If that information helps at all..

[justsomeguy]

It sounds like the database is designed so that the field you're storing text in is limited to a certain number of characters. You can use a different data type there. A text column will hold 16,384 characters, for example. There's a list of string data types and storage requirements here:http://dev.mysql.com/doc/refman/5.0/en/string-types.htmlhttp://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

But not only that, if I post too quickly like a chat window posting only a hundred characters at a time, will not post if I posted too quickly.

You would need to do some debugging. If you're using ajax requests, then check your browser's developer console and check the net tab to see the request and response from the server. You should also make sure that PHP is set up to show all error messages, and possibly use an error log instead of displaying them in the browser.

[dreadrocky]

It sounds like the database is designed so that the field you're storing text in is limited to a certain number of characters. You can use a different data type there. A text column will hold 16,384 characters, for example. There's a list of string data types and storage requirements here:http://dev.mysql.com/doc/refman/5.0/en/string-types.htmlhttp://dev.mysql.com/doc/refman/5.0/en/storage-requirements.htmlYou would need to do some debugging. If you're using ajax requests, then check your browser's developer console and check the net tab to see the request and response from the server. You should also make sure that PHP is set up to show all error messages, and possibly use an error log instead of displaying them in the browser.

 

 

I use 'longtext' in my database. I am now going to review the links you sent me. I just wondered if it would help at all. I am not very interested in a chat system, I only had it that way attempting to fix the first issue.

[dreadrocky]

Also, if it were the limitations, I believe it would still post, but cut some of the text out. Because I have had that problem with VarChar, but it still posted. My issue right now is it wont post anything at all, then all that data is lost.

[dreadrocky]

Still looking for an answer.. The above response might have answered my question, but I don't fully understand. I use longtext in my database so limitations shouldn't be an issue I wouldn't think. If I inserted from my database, it would work perfectly fine. But using PHP to insert it is still an issue. Can someone help?

[Don Jajo]

Can I pls see your code where the insertion is done?

[dreadrocky]

<form method='post' action='send.php'><label>To :</label><br><input type="text" name="to" placeholder="To"><label>From :</label><br><input type="text" name="to" placeholder="From"><label>Subject :</label><br><input type="text" name="to" placeholder="Subject"><textarea name="message"></textarea>

<?php$to = $_POST['to'];$from = $_POST['from'];$subject = $_POST['subject'];$message = $_POST['message'];$con=mysqli_connect("example.com","peter","abc123","my_db");// Check connectionif (mysqli_connect_errno()) {  echo "Failed to connect to MySQL: " . mysqli_connect_error();}mysqli_query($con,"INSERT INTO messages (to, from, subject, message)VALUES ('$to', '$from', '$subject', '$message')");mysqli_close($con);?>

This is just a code thrown together really quick. This is exactly how I would do it, without all the perfecting of the way it looks. I simply don't have my own code anymore. Because I removed it awhile back seeing as I couldn't find a solution to it.

[Don Jajo]

Looking at the above code, there is simply nothing wrong with it

Do you get the same error when you run the code above since you lost your main code?

[dreadrocky]

Looking at the above code, there is simply nothing wrong with it

Do you get the same error when you run the code above since you lost your main code?

 

 

I don't get an error at all. Everything works naturally. I fill out the form. I send it off. But when I fill out textarea if it holds more than like 200 characters the whole thing goes to waste... And by that I mean nothing gets inserted not just the text from textarea, but the whole to, from, subject, and message is not in my database.

[justsomeguy]

Since you're using mysqli, you should be using prepared statements. Your code is open to SQL injection right now. Among other things, that means that any data that contains an apostrophe would cause the query to fail. Using prepared statements would solve that problem. Also, MySQL errors do not get automatically displayed, you would need to manually check for them. The mysqli_query will return false if the query failed, and if so then you can use other mysqli functions to show the actual error message from MySQL. This has examples of doing that:http://www.php.net/manual/en/mysqli.query.phpThere are some examples of using prepared statements here:http://www.php.net/manual/en/mysqli.prepare.php

[dreadrocky]

Since you're using mysqli, you should be using prepared statements. Your code is open to SQL injection right now. Among other things, that means that any data that contains an apostrophe would cause the query to fail. Using prepared statements would solve that problem. Also, MySQL errors do not get automatically displayed, you would need to manually check for them. The mysqli_query will return false if the query failed, and if so then you can use other mysqli functions to show the actual error message from MySQL. This has examples of doing that:http://www.php.net/manual/en/mysqli.query.phpThere are some examples of using prepared statements here:http://www.php.net/manual/en/mysqli.prepare.php

 

 

Okay, I will review those links in just a moment. Would you mind explaining what SQL injection is? and is that my underlining problem that I need to fix?

[justsomeguy]

I'm not sure, but if your data has any apostrophe in it then that will always break the query. SQL injection is when people try to write SQL code into form inputs and have it executed when the form inputs are not being validated or sanitized. Most often it would be used for something like getting a system to log you in to a certain account when you don't know the password, but in some cases it can also result in data loss.

[justsomeguy]

Here's an example of a login:

$sql = "SELECT * FROM users WHERE username='{$_POST['username']}' AND password='{$_POST['password']}'";

If someone types in the username "admin", and the password "' OR '1" then the SQL query becomes this:

SELECT * FROM users WHERE username='admin' AND password='' OR '1'

That "OR '1'" causes a record to be returned.

[dreadrocky]

Here's an example of a login:

$sql = "SELECT * FROM users WHERE username='{$_POST['username']}' AND password='{$_POST['password']}'";

If someone types in the username "admin", and the password "' OR '1" then the SQL query becomes this:

SELECT * FROM users WHERE username='admin' AND password='' OR '1'

That "OR '1'" causes a record to be returned.

 

 

 

Oh well of course thank you for all that information. That's not quite the case either. Only myself and two other people have used this. So it's not like someone trying to hack into it, they all have access to everything. But for some unknown reason it wont post it when we write too much, and more often than not we do write more than 200 characters. - I asked some people at stackoverflow, but I got blocked for asking this question. I don't really know where else to turn, cause to me, looking at this code should work perfectly fine. I was wondering if it could be hostgator or something. So this website is pretty much my last hope. If anyone could refer me to another website would be helpful as well.

[Don Jajo]

ye, justsomeguy is hitting a point.

Even if its not open to hacking, just try to escape strings lets see (we are troubleshooting)

Edited June 3, 2014 by Don Jajo

[dreadrocky]

ye, justsomeguy is hitting a point.

Even if its not open to hacking, just try to escape strings lets see (we are troubleshooting)

 

Sure why not. I was going to look into that stuff soon. Cause it'll be a point to have it when its all done. But I'll go ahead and do that now just in case.

[justsomeguy]

Only myself and two other people have used this. So it's not like someone trying to hack into it, they all have access to everything.

That's not the point. Not sanitizing your database inputs isn't just something that's nice to have that maybe you add in the future. It is a necessary part of using a database. You have this code:

mysqli_query($con,"INSERT INTO messages (to, from, subject, message)VALUES ('$to', '$from', '$subject', '$message')");

What if the message they submit is "It's me"? Now your query looks like this:

INSERT INTO messages (to, from, subject, message)VALUES ('...', '...', '...', 'It's me')

Notice how the apostrophe broke the query? You can tell by the coloring. Now that's a SQL syntax error. You fix that the same way you fix a SQL injection, they are the exact same problem. It's not just a nice thing that you should think about getting around to doing one day, it is a requirement for an application that doesn't choke on basic errors. Frankly, it's simply sloppy to not do it. It's one of the marks of an amateur programmer, and it is (literally) the #1 cause for online applications like Wordpress getting hacked through a plugin or theme that was programmed by someone who didn't understand basic security. Since you're already using mysqli, the correct way to solve that issue is using prepared statements. Don't take offense to this, it's not personal, you came here for help and I'm trying to educate you. You would do yourself a huge favor by learning how to use prepared statements, and always using them. There are way too many tutorials and beginner programmers using things like the deprecated mysql extension without doing even the most basic sanitizing or validation. You're already using mysqli, which is good, so the next step is prepared statements.That's not the only issue with that particular query, though. "To" and "From" are both reserved words in MySQL. If you use reserved words as identifiers like table or column names then you need to put backticks around them.http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

[thescientist]

 

 

Oh well of course thank you for all that information. That's not quite the case either. Only myself and two other people have used this. So it's not like someone trying to hack into it, they all have access to everything.

 

there are people out there who literally crawl google page indexes looking for sites with forms on them just for the sake of trying out SQL injection on them. They don't have to target you specifically, they just find any page out there anywhere.

 

And it's just bad practice.

[dreadrocky]

That's not the point. Not sanitizing your database inputs isn't just something that's nice to have that maybe you add in the future. It is a necessary part of using a database. You have this code:

mysqli_query($con,"INSERT INTO messages (to, from, subject, message)VALUES ('$to', '$from', '$subject', '$message')");

What if the message they submit is "It's me"? Now your query looks like this:

INSERT INTO messages (to, from, subject, message)VALUES ('...', '...', '...', 'It's me')

Notice how the apostrophe broke the query? You can tell by the coloring. Now that's a SQL syntax error. You fix that the same way you fix a SQL injection, they are the exact same problem. It's not just a nice thing that you should think about getting around to doing one day, it is a requirement for an application that doesn't choke on basic errors. Frankly, it's simply sloppy to not do it. It's one of the marks of an amateur programmer, and it is (literally) the #1 cause for online applications like Wordpress getting hacked through a plugin or theme that was programmed by someone who didn't understand basic security. Since you're already using mysqli, the correct way to solve that issue is using prepared statements. Don't take offense to this, it's not personal, you came here for help and I'm trying to educate you. You would do yourself a huge favor by learning how to use prepared statements, and always using them. There are way too many tutorials and beginner programmers using things like the deprecated mysql extension without doing even the most basic sanitizing or validation. You're already using mysqli, which is good, so the next step is prepared statements.That's not the only issue with that particular query, though. "To" and "From" are both reserved words in MySQL. If you use reserved words as identifiers like table or column names then you need to put backticks around them.http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

 

 

 

I actually can not believe I didn't think of the " ' " before. I wasn't even reading what you said correctly. That is such a small mistake. One thing I hate most about PHP is its always something so very small that corrupts the whole thing. That may or may not be that issue. I will actually look into that in a couple hours. On that note, I am sorry I haven't replied to any of this very well, I haven't had time to test any of it because of some personal events. But I will check into it now, it could very easily be just that. If it is, or if it isn't, I -will- get back to you guys tonight. Thank you all, so much for your patience with me.

[justsomeguy]

I suspect that the only relationship with the length of the text is that longer text is more likely to have an apostrophe.

[dreadrocky]

I suspect that the only relationship with the length of the text is that longer text is more likely to have an apostrophe.

 

That does actually make a lot of sense. But I am having an issue. I don't know what do to... How do I change the apostrophe to something else? I tried a few things, but this is the most recent one I tried....

$to = $_POST['to'];$from = $_POST['from'];$msg = $_POST['message'];mysqli_query($con,"INSERT INTO messages (to, from, message)VALUES ('" .$to. "', '" .$from. "', '" .$msg. "')");

[justsomeguy]

You just need to escape it, but that's the old way. The better way is prepared statements. With a prepared statement, you first send the query to the database, with placeholders where the values go. Then you send the values. The database fills it in, and your SQL injection and sanitization issues are solved. There's a description about prepared statements and how to use them here:http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.phpThe queries use ? as a placeholder for values, then you bind the values and execute the query.

[dreadrocky]

I feel so ignorant.. It really must be all the stress. But I got it. Simple replace string. Thank you all again. This case is closed. Gahh PHP and those simple mistakes >_>

[Agony]

Thats where prepared statement comes handy.

 

This way any " or ' or special keywords wotn break ur query. They are inserted purely as a value.

A piece of code from my project for example:

    public function set_post($author,$title,$content,$id){        $db =  $this->connect();        $sql = "UPDATE posts SET author = ?, title = ?, content = ? WHERE id = ?";        $rows = null;        if ($db === false) {               $rows['error'] = "DB connection error";                        }        else{$result = $db->prepare($sql);                        if ($result === false) {                $rows['error'] = "Query syntax error";            }            else {                $result->bind_param('ssi',$author,$title,$content,$id);                $result->execute();                return true;                $result->close();            }        }         return $rows;     }

Also, you can use var_dump();

If you dont want to use prepared statements. At least separate the query itself to a new variable.

like

$sql = "INSERT INTO messages (to, from, subject, message)VALUES ('$to', '$from', '$subject', '$message')";//Then u can use the $result = mysqli_connect($con,$sql);//Alsovar_dump($sql); //to test if the whole query looks allright