Pesky Quotes

[SilverBean]

I ran into a surprising last minute problem here. Not sure if there is anything Perl/CGI can do for me or if I should be looking on the MySQL side of things.

You guessed it I've got a Perl/CGI script adding, updating a MySQL database.

My adds are fine I use a join thing and push data into a field array or value array such as:

push @fields, "username";
push @fields, "password";
push @fields, "email";

push @values, $db->quote($in{'username'});
push @values, $db->quote($in{'password'});
push @values, $db->quote($in{'email'});

$myjoin="insert into tblusernames (" . join (', ', @fields) . ") values (" . join (', ',@values) . ")";

My Updates seem to be working fine. This is a typical string that I use:

$myjoin="update tblusernames set security='$security', approved='$approved' where userid='$userid' ";

The one pesky problem I'm runing into is when the data contains double quotes(") and single quotes (') and its only during updates.

The add continues to work fine but the updates gives an error and the update is not made.

Any suggestions about how to overcome this? I probably would have to live with this as a fundamental problem and maybe do a replace in Perl for these. However the Add seems to be fine with these. So it makes me think there is something on the Perl Side I could do.

 

[brigmar]

General tip:

Don't be creating SQL statements using data submitted by a user; it's an invitation to SQL injection attacks, and you end up trying to roll your own way of making the data safe.

If you're not using the DBI module, I'd suggest doing so.

http://search.cpan.org/~timb/DBI-1.601/DBI.pm

This will allow you to work with parameterized queries which take care of the quoting and subsequent SQL-injection issues.

 

[SilverBean]

I'm game, if your willing to explain.

There was blurb about "Positioned Updates and Deletes are not supported in this version of the DBI. See the description of CursorName attribute for an alternative" looked at Cursor Type really didn't seem useful.

I found this and understood it

my $sth = $dbh->prepare( q{
INSERT INTO table (name, phone) VALUES (?, ?)
});

open FH, "<phone.csv" or die "Unable to open phone.csv: $!";
while (<FH>) {
chomp;
my ($name, $phone) = split /,/;
$sth->execute($name, $phone);
}
close FH;

$dbh->commit;
$dbh->disconnect;

Ok I could probably do it this way for my adds. I don't see how this is going to make sure I have a legal phone number with/ without area code, no letters, etc. (usual phone syntax checking stuff). Is the suggestion to take the form data into a file and then process the file?

Am I replacing "INSERT INTO table...." with "UPDATE set...." or Updates are not possible?

Getting late I have to call it a night if you could offer some more suggestions I will try to understand better.

 

[brigmar]

You're right; DBI isn't going to do the error-checking of your data, but it does take care of the idiosyncrasies of the database.

Your statements were failing with quotes; The parameterized queries take care of that.

Whether you want quotes to be legal characters in the username/password/email is up to you; using DBI and the parameters will allow you to do that, as it will encode the characters correctly.

If you want quotes (and double-quotes maybe) to be illegal characters, whereby you don't even submit the values to the database, then you'll need to detect them, and then return a message that certain characters are not allowed.

if( /"/ || /'/ )
{
  # Illegal quote character found
}

 

[SilverBean]

That was my plan B. Single quote personally myself can't think of anyway else to write a contraction oops there it is. Double quotes yea probably an out right violation/do over.

Anyway I thought I could maybe just rearrange the update so it would be impervious to the quotes like my add was.

Off the top of my head:

$myjoin="update tblusernames set ".$value[$i]."=' ".$field[$i]."' where id = $something";

if it worked then I could just set up a loop for i. At most it might just be a couple of fields AND it's mostly administration clean up stuff not going to run for the everyday user......or so I intend.

I fear it's more fundamental than that with the update using single quotes(') and I was hoping someone had a oh yeah, do this instead....

 

[travs69]

I'm confused (happens often though).. is the problem you don't want " or ' at all or that it will screw up your query statement?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[SilverBean]

That's ok it could be me too because sometimes I speak confused.

This is kind of like a blog web application where workers in the field essentially makes posts of any notes they have. A superisor in a nice comfy office may or may not "check" these entries. Usually any supervisor comments are covered by checkboxes, but there is an infamous "other" field for notes. This is really not used that often. It would be really nice if I could allow single quotes(') for contraction purposes. I can probably either flag double quotes(") as do overs/ or replace myself. The person has logged into the app and I know who they are based on that a Perl function runs ShowForUser or ShowForData. Then the submit either submitUser, or submitManager. So I know fields are coming in and what I need to do. Add User, Update for Manager.

Now interesting enough I've noticed that what I use to Add the original record is impervious to the quotes dilema. It takes either without a problem. That is the best solution because I wonder what other characters are looming out there that might cause me trouble /\|<>@$. To me the quotes would be the worse case -- the most trouble. The other cases I can either disallow or force replace.

Summary:
1. Would like to allow single quotes
2. Would like to understand why the Add allows single and double quotes, but Update does not
3. Would like to know if anybody has a slightly different rearrangement for Updates which allows single and double quotes

Plan B: Replace single quote with ` and double quote with ^. Actually dah, as I write this I might as well replace them with &#34; and &#27; I think that is the answer. So thank you for being confused and making me think -ouch-. Well at this point I might as well post and see if anybody has anything better.


My whole complete code is as follows for an update which occur exhibits the quote dilema. I belive my add is in a previous post.

$db = DBI->connect("DBI:mysql:$database:$hostname", $username, $password);
$myjoin="update tblsubject set type='$type', approved='$approved' where subjectid='$subjectid' ";
$query = $db->do($myjoin);
$db->disconnect;

As I state above in other posts, this is a minor little feature -- prototype is working fine. Actually ran into this one by myself no complaints from the field yet. I'm not going to take this whole thing apart to make a massive change, but in case my hand is forced I want to have some options available and I think I have it. Thanks for all your wonderful help and allowing me to confuse you.

 

[MillerH]

As was mentioned before, you only need to use placeholders if you to solve your encoding problems.

[url=http://perldoc.perl.org/functions/my.html][black][b]my[/b][/black][/url] [blue]$dbh[/blue] = DBI->[maroon]connect[/maroon][red]([/red][red]"[/red][purple]DBI:mysql:[blue]$database[/blue]:[blue]$hostname[/blue][/purple][red]"[/red], [blue]$username[/blue], [blue]$password[/blue][red])[/red]
	or [url=http://perldoc.perl.org/functions/die.html][black][b]die[/b][/black][/url] [red]"[/red][purple]DB connect failed: [blue]$DBI::errstr[/blue][/purple][red]"[/red][red];[/red]
[black][b]my[/b][/black] [blue]$sth[/blue] = [blue]$dbh[/blue]->[maroon]prepare[/maroon][red]([/red][red]qq{[/red][purple]UPDATE tblsubject SET type=?, approved=? WHERE subjectid=?[/purple][red]}[/red][red])[/red][red];[/red]
[blue]$sth[/blue]->[maroon]execute[/maroon][red]([/red][blue]$type[/blue], [blue]$approved[/blue], [blue]$subjectid[/blue][red])[/red] or [black][b]die[/b][/black] [blue]$dbh[/blue]->[maroon]errstr[/maroon][red];[/red]
[blue]$sth[/blue]->[maroon]finish[/maroon][red];[/red]
[blue]$dbh[/blue]->[maroon]disconnect[/maroon][red];[/red]

- Miller

 

[SilverBean]

That's what I'm talking about !!! Thanks. I was seeing this but I just needed a more compact example and then it hit me. What's the difference between a single q and double qq?

qq {UPDATE blah..., and q{INSERT blah...

Just curious.

BTW: the replace technique I discovered might have a minor flaw I think this is the way to go now that I see it.

 

[brigmar]

q{a literal string} is equivalent to 'a literal string'

qq{an interpolated $string} is equivalent to "an interpolated $string"