-1

I have this websql script (http://pastebin.com/gvJseBAn) which doesn't perform correctly.

  • If I run the statement select * from news where id=0772348890 , I get the error The conversion of the varchar value ' 0017707787068' overflowed an int column.

  • If I run the statement select * from news where id='0772348890' , I get the error Incorrect syntax near '0772348890'.

  • If I run the statement select * from news where id="0772348890" , I get Invalid column name '0772348890'

Any other variation of '#0772348890#' or #0772348890# or "#0772348890#" I have tried gives the error "incorrect column" or "incorrect syntax near ..."

Any ideas on how to fix this error, or a better method of creating a simple websql query form?

Community
  • 1
  • 1
martin
  • 11
  • 1
  • 1
    Is `id` an integer datatype in the database? If you want to store something which requires a leading zero then you'll have to store it as a string. Datatypes only allow a certain number of characters. For example in MySQL an unsigned int has a max value of 4294967295. See https://dev.mysql.com/doc/refman/5.0/en/integer-types.html – John Whish Sep 11 '15 at 09:17
  • From where does that value come? – Dan Bracuk Sep 11 '15 at 11:56

1 Answers1

2

A) the issue here is that db column will not under any conditions accept "0772348890" as a valid input because it is mismatched. The column is an "int" type (according to your first error), but your value has a padded 0 prependedto the front as in 0 772...

What is the purpose of this zero? Ordinarily prepended zeros appear in fixed length character fields where a space is not allowed. Should the value not be "772348890"?

B) Remember that ColdFusion will escape your single quotes in your query. In your second error example (where you use single quotes), this code:

<cfquery name="runsql" datasource="#Form.datasource#" timeout="30">
        #Form.sql#
</cfquery>

Produces this SQL statement:

select * from news where id=''0772348890''

Which would give you your syntax error. If you wish to successfully test your second example you will need to alter your code to:

<cfquery name="runsql" datasource="#Form.datasource#" timeout="30">
        #preservesinglequotes(Form.sql)#
</cfquery>

Preservesinglequotes() gets you past the second error issue and MSSQL's implicit conversion may strip off the prepended zero and allow the query to succeed - though I'm not sure will give you what you want.

C) Finally you should probably never do what you are trying to do - at least not in this fashion (sorry to be so direct!). Your opening up your DB to arbitrary queries from a web form. The resulting damage from even casual mistakes could be catastrophic to your data, let alone a malicious user bent on stealing or altering or using your site for malicious purposes. That's my take. :)

Mark A Kruger
  • 7,183
  • 20
  • 21
  • hi, thanks for the information and being direct. but the server i am working on is internal to a very closed amount of people like 2 :) if at all possible could i ask you what would be the best and most simplistic shell i can use for doing these type of queries ? could you offer something that i can use in place of this current one? i made the changes but still get the error of converting vchar – martin Sep 11 '15 at 15:58
  • [SSMS](https://msdn.microsoft.com/en-us/library/ms174173.aspx) ? ;-) Seriously, if the system is closed, why not just use proper database tools to access it? As Mark said, this kind of form is open for misuse - even from authorized users. Aside from the obvious (dropping of tables, databases, etc...) picture someone submitting an unintentional cross join that grinds the db to a halt, or an update without any where filters? At least with proper db tools, there is some auditing and access control. With this kind of raw web query, there is none. – Leigh Sep 11 '15 at 19:48
  • @LEIGH i came for assistance to something not a lecture on good db administration practice, if you cant offer assistance from a technical perspective, then please dont respond. – martin Sep 12 '15 at 14:37
  • @martin - Stating the risks and asking what options you have considered *is* assistance. Execution of arbitrary sql via web forms is something experienced developers typically avoid like the plague, for good reason. The fact that you attempting doing this, raises valid questions about your understanding of the available options and risks. Not sure why you seem uncomfortable with legitimate questions. However, you asked for feee advice and it was given. If you want to disregard it, {shrug} that is up to you. Just ignore it or say "Thanks, but no thanks" and move on. – Leigh Sep 12 '15 at 16:21
  • Martin - can you answer my first question? What is the purpose of the prepended "00" in your variable? what happens if you remove it? What does the underlying data look like. – Mark A Kruger Sep 18 '15 at 17:26
  • @Mark , apologies for the delay. if i remove the 00 i get the same error – martin Sep 20 '15 at 12:22
  • for which query Martin - _with_ or _without_ single quotes? – Mark A Kruger Sep 21 '15 at 13:36